Solved

MS Access 2007 compare and write to 2 different tables

Posted on 2014-11-18
9
250 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 35

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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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 “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

839 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