Complicated(?) Payroll Query Problem (1 Viewer)

[hokiewalrus]

I've been running around with this for the past 2 days but I really don't think I have the query chops I should to unravel it.

The background:
2 Tables, first holds PayrollNumber, WorkTimeIn, WorkTimeOut, and WorkDate. The second holds any adjustments to those entries called AdjustmentHours (linked to WorkTime table via 1-many). Adjustments are made for several reasons, including auto deductions for lunch and mistakes. Work Time is held by actual times, adjustments are just the number of hours to add or subtract.

The problem:
I need to write a query that will return the sum of WorkTimeOut-WorkTimeIn+AdjustmentHours, grouped by PayrollNumber, based on a WorkDate the user enters. Not all WorkTimes have adjustments, which is screwing me up.

Obviously I don't want to store any calculated values, but I can't figure it all out.

Simple payroll query, right? All I want is to type in a date and have the total hours each employee worked for that day, yet my brain seems to be fried. Any help would be most appreciated, thanks in advance.

 

[ByteMyzer]

What is the name of the Adjustments table, what are the field names, and on which field(s) are the two tables joined?

 

[pbaldy]

From the sound of it, you may need to change the join between the tables. In design view, right click on the join line to edit it. Change it to the appropriate "show all records from xxx". In SQL view, it's probably changing INNER to LEFT.

It would help if you could post a sample db, but of course it shouldn't have any personal info in it.

 

[hokiewalrus]

From the sound of it, you may need to change the join between the tables. In design view, right click on the join line to edit it. Change it to the appropriate "show all records from xxx". In SQL view, it's probably changing INNER to LEFT.

It would help if you could post a sample db, but of course it shouldn't have any personal info in it.

Ah! Genius! That was very helpful. I now have a query like so:

SELECT Format(Sum([WorkTimeOut]-[WorkTimeIn]+Nz([AdjustmentHours])),"Short Time") AS TotalWork, TableWorkTime.PayrollNumber
FROM TableWorkTime LEFT JOIN TableAdjustment ON TableWorkTime.TimeControl = TableAdjustment.TimeControl
GROUP BY TableWorkTime.PayrollNumber;

The only problem left is AdjustmentHours are coming through as 1 = 24hours and it takes the absolute value (adjustments can of course be negative) but otherwise it's giving me what I am looking for.

 

[pbaldy]

It occurred to me when I first saw the structure that you may have to do some math to find common ground. Time is normally stored as a decimal, where 8 hours would be .334. You may have to divide your adjustment hours by 24 before adding/subtracting.

 

[hokiewalrus]

Oh I love you guys, it works perfectly now. Thanks so much.