Joining Queries (1 Viewer)

[Matt Greatorex]

I have two tables. One stores details of all money travelling from A to B, one from B to A.
I have created queries 'qry_A_to_B' and 'qry_B_to_A' to get each set of information.
Since there are User IDs in each table that don't appear in the other, I'm using the following two queries to return all the TO and FROM values. By using both LEFT joins, I appear to be getting all entries.

'A to B data
'------------
SELECT
[qry_A_to_B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth
FROM
[qry_A to B Yearly]
LEFT JOIN [qry_B to A]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_A to B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth;

'B to A data
'------------
SELECT
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth
FROM
[qry_B to A Yearly]
LEFT JOIN [qry_A to B]
ON [qry_A to B Yearly].[ABUserID] = [qry_B to A Yearly].BAUserID
GROUP BY
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth;

How can I join these two, to provide a list of ALL information, from all ten columns, regardless of whether or not someone has both a TO and FROM value? i.e.
If they only have a TO entry, I want to see it; the other five columns would be blank.
If they only have a FROM entry I want to see it; the other five columns would be blank.
If they have both, I want them to line up on one line.

However I try to phrase the query, I seem to miss at least some of the information from one or both tables.

 

[KeithG]

So basically you want both sets of records in one query? Use a union query.

SQL Statement 1

Union

SQL Statement 2

 

[neileg]

I can't see an easier way that by using three queries and unioning them, a left join where the record from the right table is null, a right join where the record from the left table is null and an inner join.

 

[Matt Greatorex]

Thanks both.
Unfortunately, idiot that I am, I must still be doing something wrong.

I've tried using the following:

first sql statement
union
second sql statement

but it doesn't provide me with all ten columns.

What I'm now getting is the five column headings from the A to B query, and the data from both the A to B and B to A queries.
The B to A data is being displayed in the same columns as the A to B, just below it (which isn't ideal, obviously).

Is there a way to merge the two to return all ten column headings, along with the respective data?

 

[KeithG]

Do both queries have 10 columns?

 

[Matt Greatorex]

No, five in each, as described above.

The first has

[qry_A_to_B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth

and the second has

[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth

The data in columns ABUserID and BAUserID is often, but not always, the same (i.e. there may be values in the former that aren't in the latter and vice-versa).

What I'd like is to get

[qry_A_to_B Yearly].ABUserID,
[qry_A to B Yearly].ABMonth_of_Year,
[qry_A to B Yearly].ABYearly_Sum,
[qry_A to B Yearly].ABMonthly_Sum,
[qry_A to B].ABMonth,
[qry_B to A Yearly].BAUserID,
[qry_B to A Yearly].BAMonth_of_Year,
[qry_B to A Yearly].BAYearly_Sum,
[qry_B to A Yearly].BAMonthly_Sum,
[qry_B to A].BAMonth

in one query.

 

[KeithG]

I applogize I thought you were trying to accomplish something else. You will have to go with Neileg suggestion. That will do the trick for you.

 

[Matt Greatorex]

Thanks anyway, Keith.

Neileg, could you possibly explain a bit further? When you say 'the record from the right table is null' which column(s) from the right table are you referring to?

Forgive my ignorance in this area.

 

[neileg]

You said

there are User IDs in each table that don't appear in the other

So when you do a join, sometimes there will be a null when the record doesn't exist in both tables. You can't create a query that will return all records from both queries, so you have to build it as a union. First the records that exist in AB but not in BA (left join), then the records that exist in BA but not AB (right join) and then the records that exist in both (inner join). Each of these will return ten columns, but in the first two cases, half of the columns will be empty.

 

[Matt Greatorex]

Works great!

Thanks for the help and apologies for taking this long to reply (the powers that be have had me diverted onto other things).

Matt.