Link to home
Start Free TrialLog in
Avatar of rutjos01
rutjos01

asked on

MS Access 2007 compare and write to 2 different tables

So I have 2 tables that I am looking to compare.
They must match on 2 fields.
There are 3 other fields that if 1 of the 3 do not match I would like to write it to a new table.
If all 5 fields match I would like to write it to another new table.
Avatar of Phillip Burton
Phillip Burton

And now much experience do you have with Access?

How far have you got? what are the tables and fields?

A little more information please.
Avatar of rutjos01

ASKER

I have some experience in Access more so with match queries.  I work more in SQL.
I have loaded the two tables into access and joined the codes with the descriptions and validated that the 2 fields match in each table.  I have done the first compare with the first of the 3 other fields.

SELECT
FROM [11172014 BPS Extract With BT Description merge] INNER JOIN [11172014 Spade Extract With BT Description merge] ON ([11172014 BPS Extract With BT Description merge].CBSATTRIBUTELEVEL1 = [11172014 Spade Extract With BT Description merge].[SEWN BT1]) AND ([11172014 BPS Extract With BT Description merge].STYLE_NUMBER = [11172014 Spade Extract With BT Description merge].StyleNum) AND ([11172014 BPS Extract With BT Description merge].STYLE_ID = [11172014 Spade Extract With BT Description merge].STYLE_SURROGATE);
SOLUTION
Avatar of Gozreh
Gozreh
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Firstly, can I suggest using aliases for the tables. It will make your debugging so much easier.

Something like this (note the AS A and AS B):
SELECT *
FROM [11172014 BPS Extract With BT Description merge] AS A INNER JOIN [11172014 Spade Extract With BT Description merge] AS B ON ([A].CBSATTRIBUTELEVEL1 = b.[SEWN BT1]) AND ([A].STYLE_NUMBER = B.StyleNum) AND ([A].STYLE_ID = B.STYLE_SURROGATE);
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you!
All three ways seem to fit my need.  Phillip Burton & Pat Hartman were more informative including reasons as to why they suggested certain methods.