Solved

Reconcile Two Tables

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now