Count query 1

[owrsome]

I'm trying to count the number of records in my Vendors, Products, Specifications, and Books tables by their Topic field. Each table has a Topic field and Topics are records in a fifth table, Topics, and its field Topic is related one-to-many with each Topic field in the other four.

I want to create a table with all the Topics down the left column, the number of Vendors for each Topic down the next column, the number of Products per Topic down the next, etc. I need to show zeros.

I've tried lots of queries, placing Count in the Total: row and using the Count and Nz functions in expressions. Using only the Vendors table the results are weird numbers - several correct counts by Topic, followed by a count for the next Topic exactly four times the correct number, followed by some more correct ones. When I do get correct numbers for one table, Topics with zero values are not shown. When I add in the three other tables, the numbers wind up being the same for each table.

 

[dhookom]

You can't combine the five tables as they are. You could create totals queries by Topic for each table that counts the number of records for each unique Topic. Then build your final query based on these totals queries.

Another alternative is to use subqueries.

Duane
MS Access MVP