Query for totals by category 1

[KerryL]

Is there a way to create a query that displays the quantity of records (instead of the records themselves) based on what those records are linked to another table?

I have a fairly simple mailing list database where contacts are associated with categories in a separate table. They can be associated with more than one category.

There are 8000+ records in the contact table, 50 types of categories, and 8500 records linking the 8000 contacts to the 50 different categories.

What I'm trying to do is create a form that, when opened, simply displays each category name and the number of contacts (contact records) associated with it.

For instance, if "Boy Scouts," "Girl Scouts," "Media," "College" & "Red Cross" are all categories that have multiple contacts linked to them, the form would simply open with:

Boy Scouts: 56
Girl Scouts: 112
Media: 592
College: 143
Red Cross: 69


Obviously, I'll want to numbers to automatically change whenever contacts are edited or new ones added. And when a new category is added, its totals should also appear.

Hope that makes sense. Is it difficult to do?

Thanks,
Kerry

 

[PHV]

A starting point:
SELECT C.CategoryName, Count(*) As CountOfContacts
FROM tblLinkingCategoryContact L INNER JOIN tblCategory C ON L.CategoryID = C.CategoryID
GROUP BY C.CategoryName

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

 

[GingerR]

Are "Boy Scouts", "Girl Scouts" etc all data in the same field? Or are you saying you have 5 separate fields for each of those pieces of data? Please list your table structure (i.e. Table/Field names, field types, and sample data). Thanks!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

PHV:
I modified your SQL slightly, but when I inserted the correct table names it worked perfectly:

SELECT C.CategoryName, Count(*) AS [Number of Contacts]
FROM tblCategories AS C INNER JOIN tblContactCats AS L ON C.CategoryID = L.CategoryID
GROUP BY C.CategoryName;


But I don't understand what you did, especially the use of "C.CategoryName and "tblContactCats AS L ON GROUP BY C.CategoryName". Instead of me seeing the actual tables in design view, I see tables labeled "C" and "L". Can you help me understand that, and what the "C" & "L" do?

 

[PHV]

C and L are simply aliases to shorten the table's names ...

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

 

[KerryL]

Ginger FYI:

Each contact can be linked to numerous categories, and each of those links is a separate record in a third table. Here are the 3 main tables and the linked fields:

tblContacts
-->ContactID

tblCategories
-->CategoryID

tblContactCats
-->ContactID
-->CategoryID

tblContactCats links contacts to their categories via tblContacts.ContactID and tblCategories.CategoryID. Hope that makes sense.

PHV's SQL worked once I inserted the correct tbl names. I think all I need to do now is create a menu item for the users and display it nice.

Thank you for your response.