Solved

Reconcile Two Tables

Posted on 2006-11-25
3
218 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
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

713 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