how to count the number of records are linked to a record in another table?

[Ollie Shotton]

Hi, im doing a CD library database and i have two tables, "CD" and "CD Copies" linked by "Catalogue ID No" field. Bellow is a picture of what i mean (click the link below it if it doesnt work!):

https://picasaweb.google.com/1167658...75094040621826

I would like to return the number of "CD Coppies" a selected "CD" has, ie the number of highighted records in the picture.

Please RSVP! =)

Thanks in advance!

Ollie

[TheSmileyCoder]

It depends a bit on where you want this information used/displayed.

If its part of your code, you can use Dcount. If its something you "just" want to show in a query or form, you can use a subquery, or a secondary query joined to your first, through the ID field.

[Ollie Shotton]

i just want to display it in a form textbox so i can say "No Of Copies" = "x"

also i dont know any VB so prefer not to use that!

how would i go about making the subquery/secondary query?

Thanks
Ollie

[TheSmileyCoder]

In that case I would make a secondary query, call it qry_CountCatID for example.

The SQL would look like this:

SELECT [Catalogue ID No], 
       Count(Catalogue ID No) AS CountOfCatID
FROM tbl_YOURTABLENAME
GROUP BY Catalogue ID No;

You can then join this table to your main table by Catalogue ID No, and you have the counting field CountOfCatID available.