Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing tables (2000)

    How can I compare two similar tables to determine those records that differ or have been added? Andy.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Comparing tables (2000)

    It isn't exactly a simple task if the tables contain lots of records, but the essential process is:
    <UL><LI>create an unmatched query using the query wizard to find the records that are in Table 1 but not in Table 2
    <LI>create a second unmatched query using the query wizard to find the records that are in Table 2 but not in Table 1
    <LI>create a third select query where records exist in both tables (use an equi-join) and select only those records where Field 2 in Table 2 is not equal to Field 2 in Table1 OR Field 3 in Table 2 is not equal to Field 3 in Table 1 OR .... until you have covered all the fields. (This can get pretty messy if you have lots of fields - i.e. 10 or more requires that you add rows to the query grid to put in all the OR conditions in the criteria.)[/list]Having had to do this recently because people ended up working in two different copies of a database (an admins problem with not getting everyone mapped to the same server), it isn't much fun. [b][i]Note that this presumes you have a primary key that never changes so that you can actually do these kind of queries - if not it's a manual process! <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Wendell

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing tables (2000)

    Hi Andy

    Check out www.RogersAccessLibrary.com for the following:

    CompareTwoTables2k.mdb (50 KB) ( intermediate ) Access 2000 DAO
    This sample shows how you can programmatically compare two tables and write out the differences to a third table.

    This might get you started

    John

Posting Permissions

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