query to return all records

[Joe]

I have two tables table1 and table2, one for beginning of year data and the
other for end of year data.

Some accounts are in table one, but not in table 2 and others are in table 2
and not in table 1. need to make a query to return ALL accounts that are in
both tables and show a zero if it is not found. AN example is below.



Table 1

Begin Balance

Acct1 20

Acct2 30



=====================

Table 2

End Balance

Acct 2 25

Acct 3 35

==================================

Acct 1 is not in table 2, Acct3 is not in table 1



I need a query to return blank values for all records like this:

Beg End

Acct 1 20 0

Acct 2 30 25

Acct 3 0 35

 

[Dale Fye]

Joe,

Not sure why you would breakup your data like this, but given what you have,
try:

SELECT T1.Account, T1.BeginBalance, NZ(T2.EndBalance, 0) as EndBalance
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.Account = T2.Account
UNION ALL
SELECT T2.Account, 0 as BeginBalance, T2.EndBalance
FROM Table2 T2
LEFT JOIN Table1 T1
ON T1.Account = T1.Account
WHERE T1.Account IS NULL

The first part of this UNION query selects all of the values from T1 and
those that match from T2. If there is no matching value in T2, the NZ( )
function returns a zero for the ending balance.

The second part selects only those records from T2 that don't exist in T1.

HTH
Dale