Query to pull multiple pay rates 1

[dbar10]

I have a query to pull pay rates for workers. All workers have a base rate which pulls from WorkerPayrollQry. Works fine. Also, some workers have a custom rate for certain clients (could be higher or lower than base rate) this comes from WorkClientPayrollQry, Works fine. Problem: I joined queries with all records from WorkerPayrollQry and only records that match WorkerID from WorkerPayrollQry. Works fine until I come across a worker that has a client which should only pay base rate and the worker also has clients with special rates. Query only shows the custom rates and leaves out clients which should only pay base rate. The resulting query is called PayRateQry.

SELECT WorkerPayrollQry.DefaultWageID, WorkerPayrollQry.WorkerID, WorkerPayrollQry.AssignDateHr, WorkerPayrollQry.ExpireDateHr, WorkerPayrollQry.Hourly, WorkerPayrollQry.Mileage, WorkerPayrollQry.Salary, WorkerPayrollQry.Period, WorkClientPayrollQry.ClientID, WorkClientPayrollQry.AssignDateHr, WorkClientPayrollQry.ExpireDateHr, WorkClientPayrollQry.Hourly, WorkClientPayrollQry.Mileage
FROM WorkerPayrollQry LEFT JOIN WorkClientPayrollQry ON WorkerPayrollQry.WorkerID = WorkClientPayrollQry.WorkerID;

I cannot figure out what is wrong. Can anyone help, please?

 

[dhookom]

I would expect you need to join on both the WorkerID and the ClientID.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks DHookum. I thought of that too but the WorkClientPayrollQry does not have ClientID. That query is for all workers that don't have a special rate. I have tried several ways of bringing the clients into it but I get many records for each worker and the output has many duplicate records. Any other ideas?

 

[dbar10]

Sorry I meant WorkerPayrollQry is for all workers base rate for all clients unless they have a custom rate

 

[dhookom]

Please tell us more about your table structures and queries and what you want.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Here goes:
I have attached the queries and tables. The final query looks at the schedule and assigns pay rates for each worker by client and shift. Each worker has a base rate of pay (WorkerPayrollQry) and some have a custom rate of pay for a particular client (WorkClientPayrollQry). My problem is this, If a worker has a client with a custom Rate it pulls fine but if the same worker has another client with just a base rate it doesn’t pull that. If worker only has a base rate with no custom rate everything works fine. If worker has only custom rate clients, everything is fine. But mix the two and it only pulls the custom rate clients. I hope that you can help. Thank you very much.

WorkerPayrollQry:
SELECT WorkerWagetbl.DefaultWageID, WorkerWagetbl.WorkerID, WorkerWagetbl.AssignDateHr, WorkerWagetbl.ExpireDateHr, WorkerWagetbl.Hourly, WorkerWagetbl.Mileage, WorkerWagetbl.Salary, WorkerWagetbl.Period, [Worker table].WorkEligHoliday, [Worker table].WorkEligOvertime
FROM WorkerWagetbl INNER JOIN [Worker table] ON WorkerWagetbl.WorkerID = [Worker table].WorkerID
WHERE (((WorkerWagetbl.AssignDateHr)<=[Forms]![WorkerPayRates]![VerifiedFrom]) AND ((WorkerWagetbl.ExpireDateHr) Is Null Or (WorkerWagetbl.ExpireDateHr)>[Forms]![WorkerPayRates]![VerifiedFrom]));
WorkerWagetbl:
DefaultWageID AutoNumber
WorkerID Number
AssignDateHr Date/Time Short date
ExpireDateHr Date/Time ShortDate
Hourly Currency
Mileage Currency
Salary Currency
Period Number Lookup: Hour/Week/Month

Joined to: Worker table

WorkerID AutoNumber
WorkStatusID Number - Lookup
LastName Text
FirstName Text
Middle Text
etc. other demographic info

WorkClientPayrollQry:
SELECT WorkClientWagetbl.WorkerID, WorkClientWagetbl.ClientID, WorkClientWagetbl.AssignDateHr, WorkClientWagetbl.ExpireDateHr, WorkClientWagetbl.Hourly, WorkClientWagetbl.Mileage, WorkClientWagetbl.Period
FROM WorkClientWagetbl
WHERE (((WorkClientWagetbl.AssignDateHr) Is Null Or (WorkClientWagetbl.AssignDateHr)<=[Forms]![WorkerPayRates]![VerifiedFrom]) AND ((WorkClientWagetbl.ExpireDateHr) Is Null Or (WorkClientWagetbl.ExpireDateHr)>=[Forms]![WorkerPayRates]![VerifiedFrom]));
WorkClientWagetbl:
ClientWageID AutoNumber
WorkerID Number
ClientID Number
AssignDateHr Date/Time Short date
ExpireDateHr Date/Time Short Date
Hourly Currency
Mileage Currency
Period Number Lookup – Hourly or Salary


PayRateQry:
SELECT WorkerPayrollQry.DefaultWageID, WorkerPayrollQry.WorkerID, WorkerPayrollQry.AssignDateHr, WorkerPayrollQry.ExpireDateHr, WorkerPayrollQry.Hourly, WorkerPayrollQry.Mileage, WorkerPayrollQry.Salary, WorkerPayrollQry.Period, WorkClientPayrollQry.ClientID, WorkClientPayrollQry.AssignDateHr, WorkClientPayrollQry.ExpireDateHr, WorkClientPayrollQry.Hourly, WorkClientPayrollQry.Mileage
FROM WorkerPayrollQry LEFT JOIN WorkClientPayrollQry ON WorkerPayrollQry.WorkerID = WorkClientPayrollQry.WorkerID;

Schedule1: (Table)

SchedID AutoNumber
MasterSchedID Number
DaySchedID Number
ClientID Number
WorkerID Number
AuthID Number
JobID Number
ProgramID Number
Date Date/Time Short Date
Day Number
Start Date/Time Short Date
End Date/Time Short Date
Verified yes/No
VerifiedDate Date/Time ShortDate
Cancel Yes/No
Reason Number Lookup
NoNeed Yes/No
CancelMaster Yes/No
CancelMasterDate Date/Time Short Date
Holiday Yes/No
Mileage Number
Misc Number Lookup
MiscQty Number
PayMiscRate Currency
Pay/Bill Number Lookup

PayRateQry joined to Schedule1(table) along with supporting tables
SchedulePayrollQry:

SELECT Schedule1.SchedID, Schedule1.WorkerID, [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle AS WorkerName, [Worker table].SSN, WorkStatus.WorkStatus, Jobs.Job, Category.Category, Schedule1.ClientID AS ClientIdent, [Client Table].LastName & ", " & [Client Table].FirstName & " " & [Client Table].Middle AS ClientName, SchedDay.Day, Schedule1.Date, IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom+7) And [Date]<=(Forms!WorkerPayRates!PeriodTo+7),0,IIf([Date]>=Forms!WorkerPayRates!PeriodFrom And [Date]<=Forms!WorkerPayRates!PeriodTo,1,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-7) And [Date]<=(Forms!WorkerPayRates!PeriodTo-7),2,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-14) And [Date]<=(Forms!WorkerPayRates!PeriodTo-14),3,4)))) AS Week, Schedule1.Start, Schedule1.End, (DateDiff("n",[Start],[End]+IIf([End]<[Start],1,0))/60) AS [Hours worked], IIf([Salary]>0 And [Hours worked]>=4,8,IIf([Salary]>0 And [Hours worked]<4,[Hours worked],IIf([Salary]=0,[Hours worked],[Hours worked]))) AS [Hours Paid], Schedule1.Verified, Schedule1.VerifyDate, PayRateQry.WorkerPayrollQry.Hourly, PayRateQry.WorkerPayrollQry.Mileage, PayRateQry.Salary, PayRateQry.Period, PayRateQry.ClientID, PayRateQry.WorkClientPayrollQry.Hourly, PayRateQry.WorkClientPayrollQry.Mileage, IIf(IsNull(WorkClientPayrollQry.Hourly),WorkerPayrollQry.Hourly,WorkClientPayrollQry.Hourly) AS PayRate, Schedule1.Holiday, IIf([Holiday]=On,([Hours Paid]*[PayRate])*1.5,[Hours Paid]*[PayRate]) AS ShiftPay, IIf([Salary]>0,"Salary","Hourly") AS PayType, Schedule1.Mileage AS Miles, IIf(IsNull(WorkClientPayrollQry.Mileage),WorkerPayrollQry.Mileage,WorkClientPayrollQry.Mileage) AS MilesRate, [Miles]*[MilesRate] AS MileagePaid, Schedule1.Misc, Schedule1.MiscQty, Schedule1.PayMiscRate, [MiscQty]*[PayMiscRate] AS MiscPaid, Schedule1.[Pay / Bill]
FROM (Category INNER JOIN (WorkStatus INNER JOIN [Worker table] ON WorkStatus.WorkStatusID = [Worker table].WorkStatusID) ON Category.CategoryID = [Worker table].CategoryID) INNER JOIN (SchedDay INNER JOIN ([Client Table] INNER JOIN ((Schedule1 INNER JOIN Jobs ON Schedule1.JobID = Jobs.JobID) INNER JOIN PayRateQry ON Schedule1.WorkerID = PayRateQry.WorkerID) ON [Client Table].ClientID = Schedule1.ClientID) ON SchedDay.DayID = Schedule1.Day) ON [Worker table].WorkerID = Schedule1.WorkerID
WHERE (((Schedule1.Verified)=-1) AND ((Schedule1.VerifyDate)>=[Forms]![WorkerPayRates]![VerifiedFrom] And (Schedule1.VerifyDate)<=[Forms]![WorkerPayRates]![VerifiedTo]) AND ((PayRateQry.ClientID) Is Null Or (PayRateQry.ClientID)=[Schedule1].[ClientID]) AND ((Schedule1.[Pay / Bill])<>3 Or (Schedule1.[Pay / Bill])=4))
ORDER BY [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle, Schedule1.Date, Schedule1.Start;

 

[dhookom]

I'm not sure I would join the two payrate queries into PayRateQry. I would probably join both payrate queries into the final query so you can join the ClientID from one of the queries.

Duane
Hook'D on Access
MS Access MVP

 

[dbar10]

Thanks DHookum. It was still a bit tricky figuring out the right combination of joins, but it seems to be working perfect now. I really appreciate the help once again. Big star for ya.