Solved

MS Access 2007 compare and write to 2 different tables

Posted on 2014-11-18
9
245 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
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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 34

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now