matching tables

[Guest]

Thank you in advance …..Any help would be greatly appreciated. I have two
tables, Table one is an “Import Table†(this table is imported and can not
be modified) I did a make table query which is Table 2 (an exact match)
“Update
Table†(this table will be modified with updates). What I am trying to do is
match
one to the other and tell me what the difference is in another Query or
Table. I have over 100 columns to match up. I assume this is possible but
I'm not an advanced user so any help would be greatly appreciated .... See
below….

Table 1 “Import Tableâ€
Site Col1 col2 col3
123 1/1/07 1/1/07 1/1/07
234 1/1/07 1/1/07 1/1/07
543 1/1/07 1/1/07 1/1/07

Table 1 "Update Table"
Site Col1 col2 col3
123 1/2/07 1/1/07 1/2/07
234 1/1/07 1/4/07 1/1/07
543 1/3/07 1/1/07 1/1/07

New Queries
Site Col1 col2 col3
123 1/2/07 1/2/07
234 1/4/07
543 1/3/07

 

[John Spencer]

A query to do that for all one hundred fields at one time will be
tedious to construct.

SELECT i.Site
, IIF(i.Col1 = U.col1,Null,UCol1) as Col_1
, IIF(i.Col2 = U.col2,Null,UCol2) as Col_2
, IIF(i.Col3 = U.col3,Null,UCol3) as Col_3
....
IIF(i.Col100 = U.col100,Null,UCol100) as Col_100
FROM [Import] as i INNER JOIN [Update] as U
ON i.Site = U.Site


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Thank you so much.. It works great

A query to do that for all one hundred fields at one time will be
tedious to construct.

SELECT i.Site
, IIF(i.Col1 = U.col1,Null,UCol1) as Col_1
, IIF(i.Col2 = U.col2,Null,UCol2) as Col_2
, IIF(i.Col3 = U.col3,Null,UCol3) as Col_3
....
IIF(i.Col100 = U.col100,Null,UCol100) as Col_100
FROM [Import] as i INNER JOIN [Update] as U
ON i.Site = U.Site


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thank you in advance …..Any help would be greatly appreciated. I have two
tables, Table one is an “Import Table†(this table is imported and can not
be modified) I did a make table query which is Table 2 (an exact match)
“Update
Table†(this table will be modified with updates). What I am trying to do is
match
one to the other and tell me what the difference is in another Query or
Table. I have over 100 columns to match up. I assume this is possible but
I'm not an advanced user so any help would be greatly appreciated .... See
below….

Table 1 “Import Tableâ€
Site Col1 col2 col3
123 1/1/07 1/1/07 1/1/07
234 1/1/07 1/1/07 1/1/07
543 1/1/07 1/1/07 1/1/07

Table 1 "Update Table"
Site Col1 col2 col3
123 1/2/07 1/1/07 1/2/07
234 1/1/07 1/4/07 1/1/07
543 1/3/07 1/1/07 1/1/07

New Queries
Site Col1 col2 col3
123 1/2/07 1/2/07
234 1/4/07
543 1/3/07