tomasdlv
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help, Tom :)
ASKER
Thanks so much this worked great!!
Tom