Solved

MS Access 2007 compare and write to 2 different tables

Posted on 2014-11-18
9
252 Views
Last Modified: 2014-11-18
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.
0
Comment
Question by:rutjos01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40450144
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
 

Author Comment

by:rutjos01
ID: 40450166
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
 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 166 total points
ID: 40450208
Add Field to query [Table1].[Field1]=[Table2].[Field1] criteria False - that it do not match, then append it to new table
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40450240
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40450245
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 167 total points
ID: 40450256
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
 
LVL 37

Accepted Solution

by:
PatHartman earned 167 total points
ID: 40450265
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
 

Author Comment

by:rutjos01
ID: 40450376
Thank you!
0
 

Author Closing Comment

by:rutjos01
ID: 40450385
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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