Query Problem- Urgent Help Required

[Vp]

Dear all

I have two tables
Table T1
ID Name
1 A
2 B
3 C
4 D
5 E
Table T2
ID X1 Stat
1 1 F
1 2 F
1 3 F
1 4 F
2 1 F
2 2
2 3 F
2 4 F
3 1 F
3 2 F
3 3

I want output like this
ID Name Count of First three
record of T2 having
F Stat
1 A 3 F
2 B 2 F
3 C 2 F

He Third column display count for continus F coming from top to
bottom, if anything missed inbetween not counted in output

thanx

[Rich P]

Try this query out:

select t2.ID, t1.Name, IIF( count(*) 3, 3, count(*)) as countOfRows ,
t2.stat
from tbl2 t2 inner join tbl1 t1 on t2.ID = t1.ID
where stat is not null
group by t2.ID, t1.Name, t2.stat

I aliased your tables as t1 and t2, so in my query I renamed them tbl1
and tbl2 respectively.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Vp]

On Jul 22, 1:19*am, Rich P <[e-mail]:

Try this query out:

select t2.ID, t1.Name, IIF( count(*) 3, 3, count(*)) as countOfRows ,
t2.stat
from tbl2 t2 inner join tbl1 t1 on t2.ID = t1.ID
where stat is not null
group by t2.ID, t1.Name, t2.stat

I aliased your tables as t1 and t2, so in my query I renamed them tbl1
and tbl2 respectively.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

I m really sorry, I had done one mistake in output, just consider it
as under

ID Name Count of First three
record of T2 having
Stat
1 A 3 F
2 B 1 F
3 C 2 F