Sorry for the vague title, but I couldn't think of a snappy way to title the issue.

Here's the situation. I'm querying staff data based on who their manager is. So, give me all staff who report to Jim Bob, etc.

Thing is, under Jim Bob, some staff have salary data, some have bonus data, some have both.

Because the selection criteria for staff with salary data and staff with bonus data are completely different, I have two a query for each which I then full outer join together.

So in the report it will look like this:

Manager | # staff with bonuses | Total bonus amount | # staff with salary raise | Total salary raise |

So the # staff with bonuses and the total bonus amount come from one query, and the # of staff with salary raises and the total salary raise come from a different query.

The two queries are full outer joined based on the manager ID.

The problem occurs when a manager's staff either has no one with a bonus, or no one with a raise.

Because I'm joining the manager's ID to their name based on the bonus group, when a manager has people in the salary group but no one in the bonus group their name is not found.

The only work around I came up with was keeping two manager name fields, one joined from the salary group and one from the bonus group. When I'm filling out the report I have an IF check that says if the name is blank for the bonus group, take it from the salary group.

It works, but it's ugly.

Is there an elegant way to do a full outer join based on a common id field found in both sub groups that says, when the record isn't found on one side, copy the id field from the other side?

I'm sure this is confusing. I'll try to illustrate it.

Bonus Sub Query

Manager | #Staff with bonuses | Bonus Amount
Jim Bob | 5 | $50000


Salary Sub Query

Manager | #Staff with raises | Raise Amount
Jim Bob | 5 | $25000
Adam M | 3 | $15000

Master Query

Manager | #Staff with bonuses | Bonus Amount | #Staff with raises | Raise Amount
Jim Bob | 5 | $50000 | 5 | $25000
_ | _ | _ | 3 | $15000

See how Adam M is blank? It's because if I only check the bonus sub query for a name, his won't be on it.

The workaround I used was to hold two manager name fields in the Master Query. One from each subquery. Most of the time they are equal. But when one is missing, my report generation logic checks the other field.

What I'd like to have is a single name field in the master query, that will hold the manager name from whichever sub query has it filled out, is it possible?