Solved

Reconcile Two Tables

Posted on 2006-11-25
3
221 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:tomasdlv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 18013158
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
 

Author Comment

by:tomasdlv
ID: 18013229
Patrick,

Thanks so much this worked great!!

Tom
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 18013298
Glad to help, Tom :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Query - calculate End of the Month in Query 2 54
Balance After Payment 12 62
Menus 6 56
Add Underline to custom Caption on Label 4 36
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question