Find records with changed field data

  • Thread starter Thread starter Bradley C. Hammerstrom
  • Start date Start date
B

Bradley C. Hammerstrom

Access2000

Compare two tables to return a list of records that have differing (new
order) of FieldNoteID numbers.

tblFieldNotesOLD
FieldNoteID
FieldNote

tblFieldNotesNEW
FieldNoteID
FieldNote

The NEW table has a different, updated order for some of the field notes. I
want to be able to view the list of FieldNotes that have been re-ordered
with a new ID as compared to the OLD table.

Brad H.
 
Hi Bradley,

The easiest way to do this is to used the "Find Unmatched Query Wizard" from
the list that is displayed when you click the "New" button on the Queries
group in Access. This wizard will walk you through all the steps to compare
the two tables and return the records with altered values.

Hope this helps,
- Glen
 
Glen,

I tried this earlier and it returns nothing because the ID numbers each have
a match; it's just that the FieldNote is different on some of them. Those
are the ones I want to see. By the way, I cannot use the FieldNote field to
join the tables in a query because the fields are Memo types.

To state the problem again: Two tables have equal numbers of records with
equal data in two fields (ID and FieldNotes). One table matches the ID to a
different order of FieldNotes as compared to the other table. Return the
list of records that have a different ID-to-FieldNotes match.

Brad.
 
OK, try this:

1. Inner Join tblFieldNotesNEW with tblFieldNotesOLD on FieldNoteId
2. In the FieldNote column, use "<> [tblFieldNotesOLD].[FieldNote]" as the
criteria

This *should* return any records in which the notes do not match, although
there may be a problem with criteria in a memo field (I'm not sure). The
SQL should look something like this:

SELECT tblFieldNotesNEW.*
FROM tblFieldNotesNEW INNER JOIN tblFieldNotesOLD
ON tblFieldNotesNEW.FieldNoteId = tblFieldNotesOLD.FieldNoteId
WHERE ((tblFieldNotesNEW.FieldNote <> tblFieldNotesOLD.FieldNote));

Hope this helps,
- Glen
 
Glen,

That's it! The WHERE condition is what I didn't have. (It does work with a
Memo field)

Thanks again.

Brad H.
 
Back
Top