Solved

MS Access 2007 compare and write to 2 different tables

Posted on 2014-11-18
9
256 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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