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.
rutjos01Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PatHartmanConnect With a Mentor Commented:
You can do this entirely with queries but you will need more than one.  You will need a separate append query for each table you want to move the data to.

You should also think about why you are copying the data.  In most cases, reports, exports, forms, etc. should be based on a query.  There is no need to actually move/copy rows physically.  Since you are familiar with SQL Server, I'm sure you understand that duplicating data leaves it vulnerable to anomalies when one instance is changed and another is not changed.

For the 5-field match, use an inner join.  For the 2 + 3 query.  Use an inner join for the two columns that must match and use the Where clause to identify the partial matches.

Select ..
From a inner join b on a.fld1 = b.fld1 and a.fld2 = b.fld2
Where a.fld3 <> b.fld3 OR a.fld4 <> b.fld4 OR a.fld5 <> b.fld5;
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
 
rutjos01Author Commented:
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);
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GozrehConnect With a Mentor Commented:
Add Field to query [Table1].[Field1]=[Table2].[Field1] criteria False - that it do not match, then append it to new table
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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):
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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);
0
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Then you can have

Where iif(A.field1 <> B.field1,1,0) + iif(A.field2<> B.field2,1,0) + iif(A.field3 <> B.field3,1,0) = 1

That's your criteria for your first query.

The second query has = 0 at the end instead of =1
0
 
rutjos01Author Commented:
Thank you!
0
 
rutjos01Author Commented:
All three ways seem to fit my need.  Phillip Burton & Pat Hartman were more informative including reasons as to why they suggested certain methods.
0
All Courses

From novice to tech pro — start learning today.