Combining Queries. Complex!

[shift2076]

Alrighty...
My DB contains the following tables:
VacTab - Keeps track of crew members on vacation and people covering their shifts.
CrewRost - List of all the crew members and what crew they belong to.
CrewSched - The complete schedule of what crew is working which shift (Currently programmed out until Feb 2008)

Now, I need a query which totals regular crew hours for individuals and the overtime hours they cover for other crew members on vacation. And puts the overtime hours in the correct day/shift.

I have a working query which equates how many hours an individual works with their crew, based on the CrewSched Table. This was rather complex, because we work a combination of 8 hour and 12 hours shifts.

I also have a working query which puts the overtime hours of each crew member into the correct day/shift that they cover for crew members on vacation.

What I need is a way to combine these into a single report, where I can use conditional formatting to highlight the overtime hours. The problem is, if I use a union query, the report will treat these values as such, and not highlight the overtime hours.

If anyone has an idea as to where I can start, it would be greatly appreciated.

Thanx ,
Matt

 

[neileg]

Not sure I understand completely, but in your quries you could add a constant field that shows which table the records come from. Eg S for standard hours and O for overtime. Use this to drive your conditional formatting.

 

[shift2076]

I thought about that and that's the direction I'm moving in now.

For anyone following along because they have a similar dilemma, a union query alone didn't work, as the values for regular hours and OT hours came up as two different rows for each date. So... I wrote ANOTHER query which grouped by date and name, then summed up all the other values.

My OT hours query also produced null values instead of zeros, so I had to enclose them in Nz --- Nz([SaDayH],"0")....

Thank you Neil for the input!