Recordcount of Query results

[bfinelsen]

I currently have a simple access database with 2 tables. The first table is simply a list of available categories in relation to products. The second table list all the products with a field that specifies which category it belongs in. Within my code, I specify a query that pulls all the categories and another query that pull all products. What I am trying to do is put the total number of products in paranthesis next to the category to give the user an idea of how many are in each for example:

category1 (145)
category2 (37)
category3 (567)
etc...

How does one achieve this? Thanks in advance for any help.

[paross1 · Accepted solution]

Something like this?

SELECT c.CategoryName, COUNT(p.ProductID) AS prodCount
FROM product p
INNER JOIN category c ON c.categoryID = p.categoryID
GROUP BY c.CategoryName

Phil

[bfinelsen]

Phil,

I changed the query to make my tables and field names:

SELECT c.Categories, COUNT(p.ProductID) AS prodCount
FROM Products p
INNER JOIN Categories c ON c.categories = p.category
GROUP BY c.Categories

And what it appears to be doing is counting how many of the category names were used in the database rather than how many of each. I have 25 category names and 24 of them are being used (All is the other category used to select all of them, but it is not specified in any single record). So now, next to each category name, I have (24) listed for all of them. This seems to be a start, but not exactly what I'm trying to accomplish. What am I doing wrong?

[Dan_Bracuk]

quote:

---

Originally posted by: bfinelsen
Phil,
I changed the query to make my tables and field names:
SELECT c.Categories, COUNT(p.ProductID) AS prodCount
FROM Products p
INNER JOIN Categories c ON c.categories = p.category
GROUP BY c.Categories

And what it appears to be doing is counting how many of the category names were used in the database rather than how many of each. I have 25 category names and 24 of them are being used (All is the other category used to select all of them, but it is not specified in any single record). So now, next to each category name, I have (24) listed for all of them. This seems to be a start, but not exactly what I'm trying to accomplish. What am I doing wrong?

---

If you have 24 categories, and are showing results that indicate each category is associated with 24 products, I suspect that you are outputting the query recordcount instead of the prodCount.

But I am only guessing.

[bfinelsen]

After taking another look at my code, I realized my error and by using the suggestion you made, Phil, I was able to achieve my desired look. Thank you for the help.