Solved How to count distinct id? (2 Viewers)

[tihmir]

Hi, everyone!
I need help building a query with a column to count the distinct id. What do I mean :
That is my first query (Query1):

SELECT tbl_Inspections.inspectionDate, tbl_Inspections.employee,
tbl_Objects.city, Count(tbl_Inspections.inspction_type) AS CountOfinspction_type, tbl_Objects.object_id
FROM tbl_Objects INNER JOIN tbl_Inspections ON tbl_Objects.object_id = tbl_Inspections.object_id
GROUP BY tbl_Inspections.inspectionDate, tbl_Inspections.employee, tbl_Objects.city, tbl_Objects.object_id;

That is second query (Query2):

SELECT
tbl_Employees.employeeName,
Sum(query1.CountOfinspction_type) AS SumOfCountOfinspction_type
FROM
tbl_Employees LEFT JOIN query1 ON tbl_Employees.employeeName = query1.employee
GROUP BY
tbl_Employees.employeeName;

I need to add a column to second query to count id's. So I need to know SumOfinspction_type for each employee and how many sites were inspected.
For example: For employee "Tom" it has 3 inspections for 2 objects..

 

[June7]

Assuming data is in Access, not another db like SQLServer.

Build query that returns DISTINCT Object_ID for each employee.
Build another query using first to COUNT Object_ID for each employee.
Build query to SUM CountOfType by employee.
JOIN the last two queries on employee ID.

Inspection date and city fields will not be in the output.

 

[arnelgp]

I made small queries from Query1 (numbered 1 & 2).
then create the Final Query (FinalQ).

 

[tihmir]

arnelgp, June7,​

Thanks for the help and invaluable advice guys!
You are awesome!
arnelgp, everything works perfectly. You are great!
I have a question about that query (2_Cou). I could not understand why is that Alias (AS [%$##@_Alias])? Дoes it mean anything or did you just pick it that way?

 

[arnelgp]

the Alias was added by Access.
you can view the query in SQL view.

 

[tihmir]

the Alias was added by Access.
you can view the query in SQL view.

OK, thank you!
I looked at the query and saw that access added this name looks like a regex
I did some exercises tried to build query that returns DISTINCT Object_ID for each employee (as advised June7), using your template query and it also worked, for which I am very pleased! I read that Access-Engine does not support

SELECT count(DISTINCT....) FROM ...

that's why I used this approach

SELECT T1.employee, Count(T1.object_id) AS CounOfObjects
FROM
    (SELECT DISTINCT object_id, Query1.employee FROM Query1)  AS T1
GROUP BY T1.employee;

Many, many thanks again to both of you!