Find changes between two tables

[Guest]

Access 2003, Windows XP

Two tables, TableNew a subset of the TableOld AND with corrections in the
FName column. I need to select only those records from TableNew with a
corrected first name.

Here's the problem: Twins! Same last name, same address, different first
name. I get the entries I want, like "Smith, Stacy, Stac Y, plus extra
entries like this: Smith, Joe, Stacy, and Smith, Stacy, Joe.

Here's what I've done: Added TableNew and TableOld to a select query.
Linked LName and StreetAddress fields. Added all fields from TableNew, plus
FName field from TableOld to the grid. Criteria for TableNew.FName is "<>
TableOld!FName"

Here's the SQL Code:

SELECT [PARENT BROCHURE DATA].FName, [PARENT BROCHURE DATA_check].FName,
[PARENT BROCHURE DATA].LName, [PARENT BROCHURE DATA].Street
FROM [PARENT BROCHURE DATA] INNER JOIN [PARENT BROCHURE DATA_check] ON
([PARENT BROCHURE DATA].Street = [PARENT BROCHURE DATA_check].Street) AND
([PARENT BROCHURE DATA].LName = [PARENT BROCHURE DATA_check].LName)
WHERE ((([PARENT BROCHURE DATA].FName)<>[PARENT BROCHURE
DATA_check]![FName]));

I've tried various sort orders and creating unique keys for the underlying
tables based on FName, LName and Address. No diff. Help, please.

 

[[MVP] S.Clark]

Create an additional field in the table. Either 'Corrected' or 'Twin'.
Include or Exclude the records, based on the path you choose.

 

[Guest]

Good idea, but that's not practical here. Table has 96,000 records. The
main issue is getting Access to keep data from the same records together in
the query results.

The twins Smith, Stacy and Smith, Joe have identical listings in TableNew
and TableOld. I'm just getting a crossed result when my query includes
TableNew!Fname and TableOld!FName: Smith, Stacy, Joe and Smith, Joe, Stacy.
Is this clear? Can I nest queries to keep this from happening?

Create an additional field in the table. Either 'Corrected' or 'Twin'.
Include or Exclude the records, based on the path you choose.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

Access 2003, Windows XP

Two tables, TableNew a subset of the TableOld AND with corrections in the
FName column. I need to select only those records from TableNew with a
corrected first name.

Here's the problem: Twins! Same last name, same address, different first
name. I get the entries I want, like "Smith, Stacy, Stac Y, plus extra
entries like this: Smith, Joe, Stacy, and Smith, Stacy, Joe.

Here's what I've done: Added TableNew and TableOld to a select query.
Linked LName and StreetAddress fields. Added all fields from TableNew,
plus
FName field from TableOld to the grid. Criteria for TableNew.FName is "<>
TableOld!FName"

Here's the SQL Code:

SELECT [PARENT BROCHURE DATA].FName, [PARENT BROCHURE DATA_check].FName,
[PARENT BROCHURE DATA].LName, [PARENT BROCHURE DATA].Street
FROM [PARENT BROCHURE DATA] INNER JOIN [PARENT BROCHURE DATA_check] ON
([PARENT BROCHURE DATA].Street = [PARENT BROCHURE DATA_check].Street) AND
([PARENT BROCHURE DATA].LName = [PARENT BROCHURE DATA_check].LName)
WHERE ((([PARENT BROCHURE DATA].FName)<>[PARENT BROCHURE
DATA_check]![FName]));

I've tried various sort orders and creating unique keys for the underlying
tables based on FName, LName and Address. No diff. Help, please.