Count Number of People Per Product

[Lorlai]

I am trying to create a query that will count the number of people that belong to a group. There can be many different groups that belong to a specific product number.

Product Number 12345
Group: psmith-jjones-bob
bhills-kking-ted


psmith-jjones
hilqert-bhills


Product Number 67890
Group: hilqert-bhills
maybe-mjones
psmith-jjones
dnobel-dwho


I want to have a count of how many members of the group belong to the product. For example, for product 12345, there are 2 psmith-jjones* members, 1 bhills-kking* and 1 hilger-bhills*. For product 67890, there are 1 each of the group members.

I am having problems figuring out how to create these counts by project number. Does anyone have an insight into this? I've always been terrible at the count logic!

Thank you!

[Rod]

Usually your requirement is satisfied by using the combination of the 'Count' function and the GROUP BY clause in your SQL. However the example you post does not seem to conform to normalised relational database design.

Please supply a copy of your database design.

[Lorlai]

I have managed to create a select query that does what I want, but can't seem to incorporate it into the total table.

SELECT Product_Number, Count(*) AS Product_HeadCount
FROM Resource_Pool
WHERE (((Resource_Pool.Resource_Pool_Group) Like "*TSmith*"))
GROUP BY Resource_Pool.Product_Number;

Which returns data:
ProductID Headcount
12345 20
6789 08
23452 16
67894 05
57923 26

This gets me the numbers that I want. I can't seem to pull this through a table though. I have tried a sub query:

SELECT DISTINCT Resource_Data.Product_Number, Resource_Data.Product_Manager_Name, Resource_Data.Resource_Pool_Group, Manager.Team, Resource_Data.[Resource_Pool_Group_Manager], (SELECT Product_Number, Count(*) AS Product_HeadCount
FROM Resource_Pool
WHERE (((Resource_Pool.Resource_Pool_Group) Like "*TSmith*"))
GROUP BY Resource_Pool.Product_Number) AS Product_Headcount
FROM Resource_Data INNER JOIN Manager ON Resource_Data.Product_Manager_Name = Manager.Manager_Name
WHERE (((Resource_Data.Product_Manager_Name)="Albert King"));

I keep getting an error: "At most one record can be returned by this subquery". I want my data to appear:

ProductID Manager GroupName TeamName Headcount
12345 Bob Smith TSmith Sharks 10

Any clue on how to get around this error?

[Rod]

Ouch, my head hurts! It took me a while to understand what you are trying to do.

I think you have inserted the count/group SQL in the wrong place in your compound query. If you think about it, the count/group SQL produces a result set - or table if you like - with two columns, product number and headcount. You can, and should, treat this SQL just like a table and specify joins to it. Basically the SQL engine will process this inner query first and then process the outer query.

What type of join, INNER, LEFT or RIGHT I leave to you but the joined column is obviously product number. Hence your compound SQL should look something like the following:

SELECT DISTINCT Resource_Data.Product_Number,
Resource_Data.Product_Manager_Name, Resource_Data.Resource_Pool_Group,
Manager.Team, Resource_Data.[Resource_Pool_Group_Manager],
Product_HeadCount
FROM Manager INNER JOIN
(Resource_Data INNER JOIN
(SELECT Product_Number, Count(*) AS Product_HeadCount
FROM Resource_Pool
WHERE Resource_Pool.Resource_Pool_Group Like"*TSmith*") AS HeadCount
ON Resource_Data.Product_Number = HeadCount_Product_Number) AS ExtendedHeadCount
ON Manager.Manager_Name = ExtendedHeadCount.Product_Manager_Name
WHERE Resource_Data.Product_Manager_Name="Albert King";

I have not tested this and it's very much off the top of my head.

Tell you what: Save your count/group query and give it a name. In the query design grid show the tables: Resource_Data and Manager. Then show the count/group query you have just saved. Join it to the tables as desired and complete the grid with desired columns and selection criteria. See if you end up with something similar to the above.