Solved Count by one Query (1 Viewer)

[Sarah.M]

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
please help me!
Sample Attachment

 

[plog]

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.

 

[XPS35]

As said, you can't actually do this in one query. If you really want to, you can try something like:

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;

 

[June7]

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.

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;

 

[arnelgp]

you can also create forms to show your info

 

[Sarah.M]

you can also create forms to show your info

I like it
Awesome! Thanks!