Query on 2 other queries

B

Bob Groger

Hello,

I am working on a payroll reporting project that has mushroomed into
something way over my head. I need to figure regular hours and overtime
hours per week (among other things), and combine them into a report with
Week 1 hours, Week 2 hours, and Total Hours. This is all working except in
the case where an employee has no timesheets for one of the 2 weeks, as can
happen if they are newly hired or terminated. Can someone look at the logic
and see if there is a solution that I am overlooking? It seems to me that
"WHERE" returns nothing if there are no timesheets.
Thanks,

Bob Groger

Week 1 query:
SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First Name],
Sum(Timesheet.Total_Paid_Time) AS

SumOfTotal_Paid_Time,

IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])<=40),(([SumOfTotal_Paid_Time])-([SumO

fOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])),(40-([SumOfOP_Hol]+[SumOfOP_Vac]+[SumOfOP_Other])))
AS Reg_Hours,

IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol])>40),(([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP

_Hol])-40),0) AS OT_Hours, Sum(Timesheet.OP_Trainee) AS SumOfOP_Trainee,
Sum(Timesheet.OP_VAC) AS SumOfOP_VAC,

Sum(Timesheet.OP_HOL) AS SumOfOP_HOL, Sum(Timesheet.OP_Other) AS
SumOfOP_Other,

Sum(Abs([Timesheet.Training_inst]=True)) AS Trainer_Count,
Sum(Abs([Timesheet.Training_Stud]=True)) AS

Trainee_Count
FROM Employees LEFT JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between [forms].[dateentry].[wk1start] And
[forms].[dateentry].[wk1end]) AND

((Employees.Payroll_Company)="RP"))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name]
ORDER BY Employees.Empl_ID;

Week 2 Query:
SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First Name],
Sum(Timesheet.Total_Paid_Time) AS SumOfTotal_Paid_Time,
IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])<=40),(([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])),(40-([SumOfOP_Hol]+[SumOfOP_Vac]+[SumOfOP_Other])))
AS Reg_Hours,
IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol])>40),(([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol])-40),0)
AS OT_Hours, Sum(Timesheet.OP_Trainee) AS SumOfOP_Trainee,
Sum(Timesheet.OP_VAC) AS SumOfOP_VAC, Sum(Timesheet.OP_HOL) AS SumOfOP_HOL,
Sum(Timesheet.OP_Other) AS SumOfOP_Other,
Sum(Abs([Timesheet.Training_inst]=True)) AS Trainer_Count,
Sum(Abs([Timesheet.Training_Stud]=True)) AS Trainee_Count
FROM Employees LEFT JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between [forms].[dateentry].[wk2start] And
[forms].[dateentry].[wk2end]) AND ((Employees.Payroll_Company)="RP"))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name]
ORDER BY Employees.Empl_ID;

Total Query:
SELECT [RP TImesheets Week 1].Driver_ID, [RP TImesheets Week 1].[Last Name],
[RP TImesheets Week 1].[First Name],

[RP TImesheets Week 1].SumOfTotal_Paid_Time, [RP TImesheets Week
1].Reg_Hours, [RP TImesheets Week 1].OT_Hours,

[RP TImesheets Week 1].SumOfOP_Trainee, [RP TImesheets Week 1].SumOfOP_VAC,
[RP TImesheets Week 1].SumOfOP_HOL,

[RP TImesheets Week 1].SumOfOP_Other, [RP TImesheets Week 1].Trainer_Count,
[RP TImesheets Week 1].Trainee_Count,

[RP TImesheets Week 2].SumOfTotal_Paid_Time, [RP TImesheets Week
2].Reg_Hours, [RP TImesheets Week 2].OT_Hours,

[RP TImesheets Week 2].SumOfOP_Trainee, [RP TImesheets Week 2].SumOfOP_VAC,
[RP TImesheets Week 2].SumOfOP_HOL,

[RP TImesheets Week 2].SumOfOP_Other, [RP TImesheets Week 2].Trainer_Count,
[RP TImesheets Week 2].Trainee_Count
FROM [RP TImesheets Week 1] INNER JOIN [RP TImesheets Week 2] ON [RP
TImesheets Week 1].Driver_ID = [RP

TImesheets Week 2].Driver_ID;
 
C

Crystal

Hi Bob,

In the Total Query, include the Employees table as well as
the 2 queries you made for Week 1 and Week 2. In your join
lines, right-click, choose properties, and choose to see all
records from Employees

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access


Bob said:
Hello,

I am working on a payroll reporting project that has mushroomed into
something way over my head. I need to figure regular hours and overtime
hours per week (among other things), and combine them into a report with
Week 1 hours, Week 2 hours, and Total Hours. This is all working except in
the case where an employee has no timesheets for one of the 2 weeks, as can
happen if they are newly hired or terminated. Can someone look at the logic
and see if there is a solution that I am overlooking? It seems to me that
"WHERE" returns nothing if there are no timesheets.
Thanks,

Bob Groger

Week 1 query:
SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First Name],
Sum(Timesheet.Total_Paid_Time) AS

SumOfTotal_Paid_Time,

IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])<=40),(([SumOfTotal_Paid_Time])-([SumO

fOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])),(40-([SumOfOP_Hol]+[SumOfOP_Vac]+[SumOfOP_Other])))
AS Reg_Hours,

IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol])>40),(([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP

_Hol])-40),0) AS OT_Hours, Sum(Timesheet.OP_Trainee) AS SumOfOP_Trainee,
Sum(Timesheet.OP_VAC) AS SumOfOP_VAC,

Sum(Timesheet.OP_HOL) AS SumOfOP_HOL, Sum(Timesheet.OP_Other) AS
SumOfOP_Other,

Sum(Abs([Timesheet.Training_inst]=True)) AS Trainer_Count,
Sum(Abs([Timesheet.Training_Stud]=True)) AS

Trainee_Count
FROM Employees LEFT JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between [forms].[dateentry].[wk1start] And
[forms].[dateentry].[wk1end]) AND

((Employees.Payroll_Company)="RP"))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name]
ORDER BY Employees.Empl_ID;

Week 2 Query:
SELECT Employees.Empl_ID, Employees.[Last Name], Employees.[First Name],
Sum(Timesheet.Total_Paid_Time) AS SumOfTotal_Paid_Time,
IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])<=40),(([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol]+[SumOfOP_Other])),(40-([SumOfOP_Hol]+[SumOfOP_Vac]+[SumOfOP_Other])))
AS Reg_Hours,
IIf((([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol])>40),(([SumOfTotal_Paid_Time])-([SumOfOP_Vac]+[SumOfOP_Hol])-40),0)
AS OT_Hours, Sum(Timesheet.OP_Trainee) AS SumOfOP_Trainee,
Sum(Timesheet.OP_VAC) AS SumOfOP_VAC, Sum(Timesheet.OP_HOL) AS SumOfOP_HOL,
Sum(Timesheet.OP_Other) AS SumOfOP_Other,
Sum(Abs([Timesheet.Training_inst]=True)) AS Trainer_Count,
Sum(Abs([Timesheet.Training_Stud]=True)) AS Trainee_Count
FROM Employees LEFT JOIN Timesheet ON Employees.Empl_ID =
Timesheet.Driver_ID
WHERE (((Timesheet.Date) Between [forms].[dateentry].[wk2start] And
[forms].[dateentry].[wk2end]) AND ((Employees.Payroll_Company)="RP"))
GROUP BY Employees.Empl_ID, Employees.[Last Name], Employees.[First Name]
ORDER BY Employees.Empl_ID;

Total Query:
SELECT [RP TImesheets Week 1].Driver_ID, [RP TImesheets Week 1].[Last Name],
[RP TImesheets Week 1].[First Name],

[RP TImesheets Week 1].SumOfTotal_Paid_Time, [RP TImesheets Week
1].Reg_Hours, [RP TImesheets Week 1].OT_Hours,

[RP TImesheets Week 1].SumOfOP_Trainee, [RP TImesheets Week 1].SumOfOP_VAC,
[RP TImesheets Week 1].SumOfOP_HOL,

[RP TImesheets Week 1].SumOfOP_Other, [RP TImesheets Week 1].Trainer_Count,
[RP TImesheets Week 1].Trainee_Count,

[RP TImesheets Week 2].SumOfTotal_Paid_Time, [RP TImesheets Week
2].Reg_Hours, [RP TImesheets Week 2].OT_Hours,

[RP TImesheets Week 2].SumOfOP_Trainee, [RP TImesheets Week 2].SumOfOP_VAC,
[RP TImesheets Week 2].SumOfOP_HOL,

[RP TImesheets Week 2].SumOfOP_Other, [RP TImesheets Week 2].Trainer_Count,
[RP TImesheets Week 2].Trainee_Count
FROM [RP TImesheets Week 1] INNER JOIN [RP TImesheets Week 2] ON [RP
TImesheets Week 1].Driver_ID = [RP

TImesheets Week 2].Driver_ID;
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert Null to Zero in query 1
Calculating Overtime on report 2
Running an update query based on aggregate result of a select quer 4
Running totals on different worksheet 2
A query based on information in previous queries 4
Combining a 2-D column graph and a 2-D line graph on the same char 3
third table relationship and query on form 3
enter data into a table that is based on 2 other tables 1

Top