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

**TheSmileyCoder** · Mar 14 '11, 07:44 PM

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** · Mar 14 '11, 08:30 PM

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** · Mar 14 '11, 08:59 PM

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

The SQL would look like this:

Code:

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.

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

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

Comment

Comment

Comment