Solved Hi, Can you please help me in creating a query for payroll?

[Khurramshaikh47]

When I am running this query using “Between [Enter Start Date:] And [Enter End Date:]”.
Employee name, Designation, Department Attendance status (Including Present, Absent, Sunday, and Other Holiday) is repeating for every date.

I need the following output:

• 1-time Employee Name, Employee ID, Designation, and Department.
• Total Number of Absents for each employee in Att_Status
• Sum of overtime hours of each employee in field OTH_SH.

Thank you very much in advance!

 

[Ranman256]

you have ATTDATE as GROUP BY,
uncheck it and change it to WHERE
(cause you don't want to see every date)

 

[plog]

You should do this by using 3 sub-queries:

sub1 - build a query using just the Attendance table. Bring down every field and use the current criteria you have underneath the Att_Date field (the ones where users enter dates). This query will limit the data to just the records you want based on the input timeframe.

sub2 - using sub1 as the source change the query to a Totals query (click the Sigma/Summation symbol), bring down the EmployeeID field twice and change one of them from GROUP BY to COUNT. Bring down the Att_Status field once, change it from GROUP BY to WHERE and make its criteria "Absent". This query will count the absences per employee.

sub3 - using sub1 as the source, change the query to a Totals query, bring down EmployeeID and OTH_SH. Change the GROUP BY under OTH_SH to SUM. This query will calculate total overtime hours per employee.

Now, you are ready to bring all the pieces together and make your query. Bring in Employe_Record, sub2 and sub3 . JOIN sub2 to Employee_Record via the EmployeeID fields and then change that JOIN to the option that shows all records from Employee_Record. Do the exact same thing to JOIN sub3 to Employee_Record. Then, bring down all the fields you want to for your query--any fiedl you want from Employee-Record and then the absences and total overtime hours from sub2 and sub3 respectively. This query should not be a Totals query, do not click the Sigma/Summation. Run it and you will have the results you want.

 

[arnelgp]

SELECT A.Employee_ID, A.Employee_Name, A.Designation, A.Department,
Count(IIF(B.Att_Status="Present",1,Null)) As TotalPresent,
Count(IIF(B.Att_Status="Absent", 1, Null)) As TotalAbsent,
Sum(B.Oth_SH) As TotalOT
FROM Employee_Record AS A LEFT JOIN
Attendance AS B
ON A.Employee_ID = B.Employee_ID
WHERE B.Att_Date BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY A.Employee_ID, A.Employee_Name, A.Designation, A.Department

 

[Khurramshaikh47]

you have ATTDATE as GROUP BY,
uncheck it and change it to WHERE
(cause you don't want to see every date)

Thank you very much Ranman

 

[Khurramshaikh47]

You should do this by using 3 sub-queries:

sub1 - build a query using just the Attendance table. Bring down every field and use the current criteria you have underneath the Att_Date field (the ones where users enter dates). This query will limit the data to just the records you want based on the input timeframe.

sub2 - using sub1 as the source change the query to a Totals query (click the Sigma/Summation symbol), bring down the EmployeeID field twice and change one of them from GROUP BY to COUNT. Bring down the Att_Status field once, change it from GROUP BY to WHERE and make its criteria "Absent". This query will count the absences per employee.

sub3 - using sub1 as the source, change the query to a Totals query, bring down EmployeeID and OTH_SH. Change the GROUP BY under OTH_SH to SUM. This query will calculate total overtime hours per employee.

Now, you are ready to bring all the pieces together and make your query. Bring in Employe_Record, sub2 and sub3 . JOIN sub2 to Employee_Record via the EmployeeID fields and then change that JOIN to the option that shows all records from Employee_Record. Do the exact same thing to JOIN sub3 to Employee_Record. Then, bring down all the fields you want to for your query--any fiedl you want from Employee-Record and then the absences and total overtime hours from sub2 and sub3 respectively. This query should not be a Totals query, do not click the Sigma/Summation. Run it and you will have the results you want.

Thank you very much dear plog, Let me try this! I'll tell you after completing it successfully.

 

[Khurramshaikh47]

SELECT A.Employee_ID, A.Employee_Name, A.Designation, A.Department,
Count(IIF(B.Att_Status="Present",1,Null)) As TotalPresent,
Count(IIF(B.Att_Status="Absent", 1, Null)) As TotalAbsent,
Sum(B.Oth_SH) As TotalOT
FROM Employee_Record AS A LEFT JOIN
Attendance AS B
ON A.Employee_ID = B.Employee_ID
WHERE B.Att_Date BETWEEN [Enter Start Date] AND [Enter End Date]
GROUP BY A.Employee_ID, A.Employee_Name, A.Designation, A.Department

Thank you arnelgp for the response.

 

[Khurramshaikh47]

Can

using sub1 as the source change the query to a Totals query (click the Sigma/Summation symbol),

you please explain this?

 

[Khurramshaikh47]

You should do this by using 3 sub-queries:

sub1 - build a query using just the Attendance table. Bring down every field and use the current criteria you have underneath the Att_Date field (the ones where users enter dates). This query will limit the data to just the records you want based on the input timeframe.

sub2 - using sub1 as the source change the query to a Totals query (click the Sigma/Summation symbol), bring down the EmployeeID field twice and change one of them from GROUP BY to COUNT. Bring down the Att_Status field once, change it from GROUP BY to WHERE and make its criteria "Absent". This query will count the absences per employee.

sub3 - using sub1 as the source, change the query to a Totals query, bring down EmployeeID and OTH_SH. Change the GROUP BY under OTH_SH to SUM. This query will calculate total overtime hours per employee.

Now, you are ready to bring all the pieces together and make your query. Bring in Employe_Record, sub2 and sub3 . JOIN sub2 to Employee_Record via the EmployeeID fields and then change that JOIN to the option that shows all records from Employee_Record. Do the exact same thing to JOIN sub3 to Employee_Record. Then, bring down all the fields you want to for your query--any fiedl you want from Employee-Record and then the absences and total overtime hours from sub2 and sub3 respectively. This query should not be a Totals query, do not click the Sigma/Summation. Run it and you will have the results you want.

I've created sub1 please check this.

 

[plog]

You need to add the date criteria to this query.

 

[Khurramshaikh47]

You need to add the date criteria to this query.

I have created sub1, sub2 and sub3 according to your instructions. I got total number of absents of each employee, and total overtime hours for each employee. Its working! Thank you so much. Please guide me which criteria should I add to sub1?

 

[plog]

The date criteria from the query in your initial post of this thread.

sub3 isn't as I prescribed. Reread my instructions.

 

[Khurramshaikh47]

You should do this by using 3 sub-queries:

sub1 - build a query using just the Attendance table. Bring down every field and use the current criteria you have underneath the Att_Date field (the ones where users enter dates). This query will limit the data to just the records you want based on the input timeframe.

sub2 - using sub1 as the source change the query to a Totals query (click the Sigma/Summation symbol), bring down the EmployeeID field twice and change one of them from GROUP BY to COUNT. Bring down the Att_Status field once, change it from GROUP BY to WHERE and make its criteria "Absent". This query will count the absences per employee.

sub3 - using sub1 as the source, change the query to a Totals query, bring down EmployeeID and OTH_SH. Change the GROUP BY under OTH_SH to SUM. This query will calculate total overtime hours per employee.

Now, you are ready to bring all the pieces together and make your query. Bring in Employe_Record, sub2 and sub3 . JOIN sub2 to Employee_Record via the EmployeeID fields and then change that JOIN to the option that shows all records from Employee_Record. Do the exact same thing to JOIN sub3 to Employee_Record. Then, bring down all the fields you want to for your query--any fiedl you want from Employee-Record and then the absences and total overtime hours from sub2 and sub3 respectively. This query should not be a Totals query, do not click the Sigma/Summation. Run it and you will have the results you want.

I've join sub2 to Employee_Record and sub3 to Employee_Record via EmployeeID then change the in the join Property All records from Employee Record. When I am running this Query an error appears. Please check attached screenshots.

 

[Khurramshaikh47]

You should do this by using 3 sub-queries:

sub1 - build a query using just the Attendance table. Bring down every field and use the current criteria you have underneath the Att_Date field (the ones where users enter dates). This query will limit the data to just the records you want based on the input timeframe.

sub2 - using sub1 as the source change the query to a Totals query (click the Sigma/Summation symbol), bring down the EmployeeID field twice and change one of them from GROUP BY to COUNT. Bring down the Att_Status field once, change it from GROUP BY to WHERE and make its criteria "Absent". This query will count the absences per employee.

sub3 - using sub1 as the source, change the query to a Totals query, bring down EmployeeID and OTH_SH. Change the GROUP BY under OTH_SH to SUM. This query will calculate total overtime hours per employee.

Now, you are ready to bring all the pieces together and make your query. Bring in Employe_Record, sub2 and sub3 . JOIN sub2 to Employee_Record via the EmployeeID fields and then change that JOIN to the option that shows all records from Employee_Record. Do the exact same thing to JOIN sub3 to Employee_Record. Then, bring down all the fields you want to for your query--any fiedl you want from Employee-Record and then the absences and total overtime hours from sub2 and sub3 respectively. This query should not be a Totals query, do not click the Sigma/Summation. Run it and you will have the results you want.

I've join sub2 to Employee_Record and sub3 to Employee_Record via EmployeeID then change the in the join Property All records from Employee Record. When I am running this Query an error appears. Please check attached screenshots.

Should I create 4th Query for Employee_Record? and Then combine sub2, sub3 and Employee_Record Query in 5th Query? I am sorry I am confused. Please guide me.

 

[plog]

In your final query you did not JOIN sub2 correctly. You did JOIN sub3 correctly. Make the JOIN to sub2 look like the one to sub3.

sub2 should be have the same JOIN as sub3--you should show all records from Employee_Record and just those records that match in sub2

 

[Khurramshaikh47]

In your final query you did not JOIN sub2 correctly. You did JOIN sub3 correctly. Make the JOIN to sub2 look like the one to sub3.

sub2 should be have the same JOIN as sub3--you should show all records from Employee_Record and just those records that match in sub2

Please check this video clip. I am trying to join sub2 but its not happening.

 

[plog]

I can't get that video to shwo anything.

You need to right click on the JOIN line between the 2 tables, then select the option to show all from Employee_Record. Can you upload a copy of the database?

 

[Khurramshaikh47]

I can't get that video to shwo anything.

You need to right click on the JOIN line between the 2 tables, then select the option to show all from Employee_Record. Can you upload a copy of the database?

#Solved
It was a task for my new job it was very important me to continue my job. Now I'll just create its report, print and submit tomorrow.
Thanks you so much to all for helping me! Special thanks to Mr. plog! Your way of explaining is excellent.