2 Queries in 1

[SvenS.]

Hi NG,

As a newbie, I've constructed 2 queries where Payments on
Accounts (ID_A) and subaccounts (ID_B) are added. This
works fine. One query is for the planned budget and one
for the running payments.

Now I want the accounts and subaccounts to have the
payments (1st query) and budget (2nd query) next to each
other in one query. When I put both into one query I
either get the combinations of the ID_As and Bs several
times or, if I join the tables, get sums that are
multiplied many times compared to what they should
actually be - like 36.00 instead of 2.00 ...

I hope that was understandable - Here's an example:
Q1
ID_A ID_B Amount
1 1 1.00
1 2 4.00
2 1 2.00

Q2
ID_A ID_B Amount
1 1 1.00
2 1 1.00
3 1 2.00

The result that I want is -
Q1
ID_A ID_B Q1 Q2
1 1 1.00 1.00
1 2 4.00 0.00
2 1 2.00 1.00
3 1 0.00 2.00

The result I get is something like this-

ID_A ID_B Q1 Q2
1 1 1.00 1.00
1 1 1.00 1.00
2 1 2.00 1.00
3 1 1.00 2.00
2 1 2.00 1.00
2 1 2.00 1.00
3 1 1.00 2.00

I'm sure this can't be too hard, however I'm having great
difficulties getting it to work - Thanks in advance,
Sven S.

 

[Tom Ellison]

Dear Sven:

You could probably build a new query from the tables to do this, but
it would probably be easiest to build a new query based on the two
existing query.

The combination of the results you want is called a FULL OUTER JOIN.
Access Jet (I assume that's what you're using) doesn't support this
directly, but it can be done as a union of two other joins, a LEFT
JOIN and a RIGHT JOIN.

In addition, you appear to want to change the NULLs that would result
from "missing" information into zeros.

You can do this:

SELECT Q1.ID_A, Q1.ID_B, Q1.Amount AS Q1, Nz(Q2.Amount, 0) AS Q2
FROM Q1
LEFT JOIN Q2 ON Q2.ID_A = Q1.ID_A AND Q2.ID_B = Q1.ID_B
UNION ALL
SELECT Q2.ID_A, Q2.ID_B, Nz(Q1.Amount, 0) AS Q1, Q2.Amount AS Q2
FROM Q2
LEFT JOIN Q1 ON Q1.ID_A = Q2.ID_A AND Q1.ID_B = Q2.ID_B
WHERE Q1.ID_A IS NULL

A union of the two joins produces all the desired results. However,
it would duplicate all the rows where ID_A and ID_B exist in both
queries. You could eliminate these using a UNION instead of a UNION
ALL, but it is faster to eliminate them with the WHERE clause I put on
the end. This will eliminate the same rows, but probably much more
efficiently.

To understand this better, please look at it in 3 parts:

SELECT Q1.ID_A, Q1.ID_B, Q1.Amount AS Q1, Nz(Q2.Amount, 0) AS Q2
FROM Q1
LEFT JOIN Q2 ON Q2.ID_A = Q1.ID_A AND Q2.ID_B = Q1.ID_B

SELECT Q2.ID_A, Q2.ID_B, Nz(Q1.Amount, 0) AS Q1, Q2.Amount AS Q2
FROM Q2
LEFT JOIN Q1 ON Q1.ID_A = Q2.ID_A AND Q1.ID_B = Q2.ID_B

SELECT Q2.ID_A, Q2.ID_B, Nz(Q1.Amount, 0) AS Q1, Q2.Amount AS Q2
FROM Q2
LEFT JOIN Q1 ON Q1.ID_A = Q2.ID_A AND Q1.ID_B = Q2.ID_B
WHERE Q1.ID_A IS NULL

The first part above will produce all the results based on Q1 with
information filled in from matching rows of Q2, but with 0 in the Q2
column where there is not row in Q2. That's a good start, but it
omits the rows from Q2 where there is no match in Q1.

The second part does exactly the same thing, but in reverse. It is
based on Q2 with information filled in from Q1 where it has matching
rows, and 0 where it does not. However, this would duplicate those
where ID_A and ID_B match in both sets.

You can now try using the first two parts above with UNION between
them. This should give the result you want, but may be slower than
the final version I recommended.

The third part above is the same as the second part above, but with
those duplicating rows eliminated.

You may not that I did not actually use a RIGHT JOIN as I initially
advertised. Instead, I reversed the JOIN from Q1 to Q2 so it was from
Q2 to Q1. This has always been more logical for me. My personal
preference is to always use LEFT JOIN, but there's nothing really
wrong with using RIGHT JOIN. Somehow, it just doesn't read well for
me.

Please let me know if this worked and if you need any other
assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[SvenS.]

Fantastic!
Thanks Tom. It took me a while to get into the SQL for the
union query but that was what I was after. Do most
professionals prefer using straight SQL code instead of
the design view in Access? I assume that this is the next
thing to learn if I want to use Access fully, or are
basics in VB more important?
Thanks again, Sven.

 

[Tom Ellison]

Dear Sven:

I have not dealt with the design grid for my own work in about 4 years
now, and I think many of the top guys and gals probably do too.

In any case, my answers would be much more difficult if I had to write
long descriptions of how to do them in the design view.

Also, you cannot create a UNION in design view. So there wasn't much
choice there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts