Hi,

I am using Access 2000 and have a table with 100 odd unique names, linked to another table which have the same values many times. So the relationship is one to many.

I am trying to write a query that returns the unique fields from one table and the count of these fields from the other table eg:

Sam 10
Frank 8
Josh 5

etc etc....

What I am trying to do is something along the lines of COUNTIF in Excel. Any help would be absolutely fantastic! :)
The Unique fields are in table VEN_NWDB.Grouping and Many fields are in VEN_Trade_Confirms.Grouping

I think if you group on "Sam" from VEN_NWDB and count "Sam" from VEN_Trade with an INNER Join on the "Sam" field it should work

Brian

Hi Brian,

Thanks for your response. I am quite new to Access and SQL so I am not too sure exactly where to code these GROUP & COUNT

you can do that with a Total Query. Click on the Σ in your query toolbar and you will get a new row in the lower pane, from which you can select the Group By and Count.

I would just use the query grid, add the 2 tables, join on the "Samfield", select the Samfield from both tables click on the aggregate function and GroupBy and Count functions for the correct fields.

Max beat me :mad:

FANTASTIC! Thank you both so much for your help, I have been trying to code this in SQL for ages! :D :D :D

You can now look at the SQL code and see where you went wrong :)

Brian