Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / July 2006

Tip: Looking for answers? Try searching our database.

Append NOMATCH DATA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
learning_codes@hotmail.com - 13 Jul 2006 07:39 GMT
Hi,

I use "Find Unmatched Query Wizard" from MS Access 2002 Wizard and give
me only one choice to pick "matching field" between two tables.  I have
12 tables that have 20 columns.   I'm looking for your help to give me
an example of looking all 12 tables if there is any changes from any 20
columns.   If there is a change and append the record to another "Table
- Change Data" table and record the new field where the column changes
from TABLE1 to Table 5.

Table 1
Table 2
Table 3
Table 4
Table 5
Table 6
Table 7
......

Table - Change Data (appends from any of Table 1 to 12
copy all columns (one record) plus new columns that tell where the data
source "from Table 5"

Your help would be much appreciated.
Thanks
John Spencer - 13 Jul 2006 13:05 GMT
How do you identify a record in table1 that matches a record in table 2?  Is
there one or two fields that can be used to match the records?

Is table 1 a Master Table and the remaining tables (2 to 12) subordinate
tables?  Or are you trying to find the differences between all the
combinations of tables?

To find records in T1 that are different than the record in table 2 and
display the data from both tables
SELECT "Table 1 - Table 2 Differences" as [Which Tables], T1.*, T2.*
FROM [Table 1] as T1 INNER JOIN [Table 2] as T2
ON T1.PrimaryKey = T2.PrimaryKey
WHERE
T1.FieldA <> T2.FieldA OR
T1.FieldB <> T2.FieldB OR
T1.FieldC <> T2.FieldC OR
T1.FieldD <> T2.FieldD OR
T1.FieldE <> T2.FieldE OR
T1.FieldF <> T2.FieldF OR
T1.FieldG <> T2.FieldG

Alternatively to just get T1 records that don't exist or are different.
SELECT "Table 1 - Table 2 Differences" as [Which Tables], T1.*
FROM [Table 1] as T1 LEFT JOIN [Table 2] as T2
ON T1.PrimaryKey = T2.PrimaryKey AND
T1.FieldA = T2.FieldA AND
T1.FieldB =T2.FieldB AND
T1.FieldC = T2.FieldC AND
T1.FieldD = T2.FieldD AND
T1.FieldE =T2.FieldE AND
T1.FieldF = T2.FieldF AND
T1.FieldG = T2.FieldG
WHERE T2.PrimaryKey  IS NULL

You can run into problems with both the above if the fields contain nulls.
Also, you may run into problems with trying to do 20 fields at once in the
join.

> Hi,
>
[quoted text clipped - 21 lines]
> Your help would be much appreciated.
> Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.