counting related records

[katiekat]

Hi All,

I KNOW there's a way to do this, but I can't remember how, or where would be the best place for it.

One to many relationship between Tbl A(one) & Tbl B(many). I want to be able to count how many records there are in B related to record 1 in A, etc.

It must have something to do with .count, but I've been away from access for so long I forget.

Any help would be appreciated! Thanks!
Kate

Holy tek-tips batman!:-0

 

[Databaseguy]

SELECT tblA.joinedfield, Count(tblB.joinedfield) AS CountOfWhatever
FROM tblB INNER JOIN tblA ON tblB.joinedfield = tblA.joinedfield
GROUP BY tblA.joinedfiled;

 

[PHV]

Another simpler way:
SELECT [Foreign Key field name], Count(*) As HowMany
FROM tblB
GROUP BY [Foreign Key field name];

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

 

[katiekat]

Thanks guys,

Both of these look fairly easy, but where's the best place for them?

I'm sure this should be obvious too....

Thanks so much for your help!
K

Holy tek-tips batman!:-0

 

[PHV]

In the query design window choose the SQL view pane, then paste the suggested code.

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

 

[katiekat]

Am I supposed to put something in the () in the 'count (*)' bit?

And I assume this is an entirely new query I'm making here, right?

Holy tek-tips batman!:-0

 

[PHV]

Entire new query with blue stuff amended by you:
SELECT [[blue]Foreign Key field name[/blue]], Count(*) As HowMany
FROM [blue]tblB[/blue]
GROUP BY [[blue]Foreign Key field name[/blue]];

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

 

[katiekat]

Thank you so much. Sorry for being such a pain about something so simple.

Thanks again!
Kate

Holy tek-tips batman!:-0