Trying to figure out an effective routine

[d]

Hi, all
I am trying to figure out an effective routine to comparing a number of
fields (around 20) between two tables (The two tables are identical) the
results would be the record source for a form.
The query below is a example of a routine I have been working with.Names of
tables etc have been cut down
SELECT T1.ID, “HD" AS [CF], T2.[HD] AS [CF]
FROM T1 INNER JOIN T2 ON T1.ID = T2.ID
WHERE (((T1.[HD])<>[T2].[HD]))
I have been thinking along the lines of making “HD" and [HD] into string
variables and then looping with a new field name etc until end of records.
Any help in achieving this or alternative approaches would be most
appreciated

 

[John Spencer]

Are you trying to display more than one field at a time?
How do you want to handle Null in table1 and Value in Table2 (or the other
way round)?
How do you want to handle Null in both tables?

Can you give an example record of the desired output you want? Do you just
want to know the field contents are different or do you want to display the
field contents if they are different?

HDNoMatch: IIF(T1.HD is Null and T2.HD is Null, Null,
IIF(T1.HD=T2.HD,Null,"DIFF"))

Or if the values are important to display
HDNoMatch: IIF(T1.HD is Null and T2.HD is Null, Null,
IIF(T1.HD=T2.HD,Null,T1.HID & " : " & T2.HD))

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

 

[d]

That you for the reply John


The form I have designed at the moment to take the test data from the
example query

Displays:-

The name of the field that has changed

The original data field from table1
The changed data field from table2


I will only be displaying data that has changed from the targeted fields
(around 20) so I did not think that nulls would be an issue