Reconcile Two Tables

I need to reconcile two tables in Access. I need this query to tell me what is table1 that is not in table2 and viceversa. I could do this on my own but I get tripped up where both tables have multiple columns. For example these tables contain account names, security name (like IBM, GOOG, etc...) and a column for quantity owned. So an example of my tables would look like like this:

table1:
acct      secsym      name                                       qty      price
smith,j      csusaet      AETNA US HEALTHCARE       26300      41.31
don, k      csusapd      AIR PRODUCTS & CHEMIC       5550      71.52
vlad, dv      csusatk      ALLIANT TECHSYSTEMS I       12650      78.25
mare, l      csusmo      ALTRIA GROUP INC                       9650      84.12
tuyi, l      csusaapl      APPLE COMPUTER INC.       14750      90.31
rewf, ko      csusaci      ARCH COAL INC.                       22640      34.01
ople, as     csusibm    IBM Companies                            4562       99.00

table2:
acct      Secsym      Name                                       qty      Price
smith,j      csusaet      AETNA US HEALTHCARE      26300      41.48
don, k      csusapd      AIR PRODUCTS & CHEMIC      5550      69.58
vlad, dv      csusatk      ALLIANT TECHSYSTEMS I       12650      77.73
mare, l      csusmo      Altria Group Inc                       9650      84.11
tuyi, l      csusaapl      APPLE COMPUTER INC.       14750      88.6
rewf, ko      csusaci      ARCH COAL INC.                       22640      34.03
fran, uy      csusadp      AUTOMATIC DATA PROCES      5200      49.2
pore, qa      csusba      BOEING COMPANY                      14950      91.1

So i would need this query to return:

Acct       secsym       name       table1.qty       table2.qty      difference of both
tomasdlvAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Hi tomasdlv,

SELECT t1.Acct, t1,Secsym, t1.Name, t1.qty AS Qty1, t2.qty AS Qty2, t1.qty - t2.qty AS Diff
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.Acct = t2.Acct AND t1.Secsym = t2.Secsym
UNION
SELECT t1.Acct, t1,Secsym, t1.Name, t1.qty AS Qty1, 0 AS Qty2, t1.qty AS Diff
FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.Acct = t2.Acct AND t1.Secsym = t2.Secsym
WHERE t2.Acct IS NULL
UNION
SELECT t2.Acct, t2,Secsym, t2.Name, 0 AS Qty1, t2.qty AS Qty2, -t2.qty AS Diff
FROM table2 AS t2 LEFT JOIN table1 AS t1 ON t1.Acct = t2.Acct AND t1.Secsym = t2.Secsym
WHERE t1.Acct IS NULL


This would be somewhat easier in SQL Server, where we could use a full outer join...

Regards,

Patrick
0
 
tomasdlvAuthor Commented:
Patrick,

Thanks so much this worked great!!

Tom
0
 
Patrick MatthewsCommented:
Glad to help, Tom :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.