Solved Count by one Query

Sarah.M

Member
Local time
, 01:55
Joined
Oct 28, 2021
Messages
335
Hi, Plz note, My VBA is blocked by IT for security reason (Cybersecurity).
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I have
EmployeeT
PhoneT
SectionT
I want to Count
How many employee in each Section? and
How many phone for each employee?
all of them in one Query 👀
I tried to count the Section it works but If I add phone fields, I got wrong result :cautious: :(o_O
please help me! 🙏🙋‍♀️
Sample Attachment
 

Attachments

You cannot do this in one query because you are counting at different levels (By employee and By section). What you can do is generate a report with multiple sections and get your data all on one report.

So, build a query for each dataset you want, make sure it shows correct data. Then build a report based on each query, then build another report that uses both those reports as subreports.
 
As said, you can't actually do this in one query. If you really want to, you can try something like:
Code:
SELECT SectionName AS [Section/Employee], Count(EmployeeID) AS [Employees/Phones]
FROM SectionT RIGHT JOIN EmployeeT ON SectionT.SectionID = EmployeeT.SecIDFK
GROUP BY SectionT.SectionName
UNION 
SELECT FullName, Count(PhoneID) AS CountOfPhoneID
FROM EmployeeT INNER JOIN PhoneT ON EmployeeT.EmployeeID = PhoneT.EmpIDFK
GROUP BY FullName;
 
Suggest another field in each SELECT to identify the category of Count data. First SELECT defines data types and field names. Advise not to use punctuation/special characters in naming convention - only exception is underscore.
Code:
SELECT SectionName AS [Section_Employee], Count(EmployeeID) AS [Employees_Phones], "EmpCnt" AS Cat
FROM SectionT RIGHT JOIN EmployeeT ON SectionT.SectionID = EmployeeT.SecIDFK
GROUP BY SectionT.SectionName
UNION
SELECT FullName, Count(PhoneID), "PhoneCnt"
FROM EmployeeT INNER JOIN PhoneT ON EmployeeT.EmployeeID = PhoneT.EmpIDFK
GROUP BY FullName;
 

Users who are viewing this thread

Back
Top Bottom