outer join assistance - MS access 2010

[sao123]

So...I need help with an outer join query.

I have 3 base query's from a table on employee overtime payroll data.

the first query finds the most recent Pay period date in the table (using a top 1), then joins with the second query to select all employees overtime records based on this date.

The third query analyses each employee to give an annual summary of their overtime payroll data.

since not all employees recieve overtime each week, I then do an outer join to combine the annual & most recent data.

Employee#
most recent pay peeriod date
OT from Most recent Pay period
OT Total from this year

Here is my problem, since not everyone has OT every week, the most recent pay period date does not exist in those records with only an Annual total.

How do I get that date to fill on the remaining records?

Reason being... when the report prints, the manager wants the payroll date to be at the top of every sheet... when i group on it, and it doesnt exist in every record, there are pages where the field is left blank.

 

[Markbnj]

I don't understand why having overtime is linked to having a most recent pay period. Guess I'd need to see the tables. Doesn't everyone get paid every period?

In any case, if you have a situation where an outer join is producing a null column and you want to default that to something, try COALESCE().

Overall, though, it seems like there's something odd with the query.

 

[sao123]

I don't understand why having overtime is linked to having a most recent pay period. Guess I'd need to see the tables. Doesn't everyone get paid every period?

In any case, if you have a situation where an outer join is producing a null column and you want to default that to something, try COALESCE().

Overall, though, it seems like there's something odd with the query.

In SAP, OT is given different payroll codes (OT 1.0, OT 1.5, OT 2.0, OT 2.5), which is different from normal pay (RT).
The checks are issued seperately, and on different weeks.
Pay week
OT Pay week
Pay week
OT Pay week

This is specifically a report used to monitor unit overtime... no one cares about monitoring base salary.

Plus if I had all the pay in there, the BW spreadsheet which I use as the datasource would be millions of lines, rather than just a few thousand.

 

[Markbnj]

Oh so this is SAP. What's it running on? Oracle?

Without really looking at the table structure it's hard to give a specific recommendation. I mentioned coalesce(), and I'll mention case statements, but a) they may not be both available on your DBMS, and b) I would be looking hard at the query before I resorted to either.

 

[Jeraden]

Could you join in the pay period table too? (assuming there is a table for that). Then you'll at least have a row for that period to drive a join off of.

 

[EagleKeeper]

Create a code for OT0
Use that when there is no OT and create the record.

You can then filter out the OT0 records for the report as needed