Multiple Counts 1

[jmodo]

I have a few tables. A,B,C. All 1 to many relationships. A->B->C. I am trying to get the counts of how many B records there are for each A record, and how many C records for each A record. 1 record is returned for each A record.

I need something like Select A.idA, Sum(Case When B.idA = A.idA then 1 else 0 end) as [B sum], Sum(Case When C.idB= B.idB then 1 else 0 end) as [C sum].

Am I doing this correctly? I have a much bigger statement, but this is the basic concept I am trying to extend.

Thanks!!!
~J

 

[PHV]

One way:
SELECT A.idA, (SELECT Count(*) FROM B WHERE idA=A.idA) AS [B sum], Count(C.idB) AS [C sum]
FROM (A INNER JOIN B ON A.idA = B.idA)
INNER JOIN C ON B.idB = C.idB
GROUP BY A.idA

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

Case When ... Looks like you know Transact SQL...

Access query objects work like views or inline user-defined functions, you can nest them.

qryCcount:

Select C.Bfk, Count(C.F2) As Ccount
From C
Group By C.Bfk


qryBCount:

Select A.Afk, Count(B.F2) as BCount, Sum(qryCcount.Ccount) As Ccount
From B Left Join On B.PK = qryCcount.Bfk
Group By B.Afk

I hope that makes sense... Bfk is the foreign Key to table B. B.PK is the Primary Key in B.

By the way, the way you were writing your Case When statement is similar to the Acces function IIF.