Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reconcile Two Tables

Posted on 2006-11-25
3
Medium Priority
?
229 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 93

Accepted Solution

by:
Patrick Matthews earned 1000 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 93

Expert Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

926 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