Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find similar but different records (A2K)

    I have two tables extracted from the same source but at different times. Some records will have had some fields changed since the first extraction and I want to these records. The primary key fields will be the same and the records exist in both tables. There are around 50 fields in each record and any could have been changed.

    I wondered about using 'find duplicates' then an unmatched between the 'find duplicates' & second version of the data but would appreciate any other ideas.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Find similar but different records (A2K)

    You could create a recordset based on each table, then loop though records comparing the fields. Perhaps add a marker field to one or both tables, then set this marker field if the values don't match.

    then write a query to show you the records with the marker field set.
    Regards
    John



  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Find similar but different records (A2K)

    You could use a single query joining both tables by an INNER JOIN on the primary keys, the WHERE clause would be quite large which would compare each field and separate these with an OR.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Location
    Wigan, Lancashire, England
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find similar but different records (A2K)

    Hi Guys

    This turned out to be really easy.

    First a Union Query on all fields selects one instance of each record if it is identical in both tables and two instances if any field is different.

    Find duplicates on the Union Query lists only those records that are not identical

    Viola.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •