Results 1 to 7 of 7
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Union and Duplicates (2002)

    I am attempting to find duplicate records between two tables and the recordset will eventually be used behind a form.

    I use two queries that each select 5 fields from the different tables. I then use a union query to combind the first two queries and produce a nice 131,000 sorted recordset in about 2 seconds.

    I use the duplicates wizard on the union query to find duplicates on the sorted field and wow did it ever slow down. After 20 minutes it was still running. I believe for each record, it
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union and Duplicates (2002)

    Why not create a query based on the two tables, with a join on the field on which you're testing for duplicates?

    BTW, a union query will suppress duplicate records by default, unless you specify UNION ALL. (A duplicate means that all 5 fields are equal)

  3. #3
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Union and Duplicates (2002)

    Hi Hans,

    A join would put two customers on the same records. With a union I put them on separate records but combine the two tables and then browse each record via a form.

    Would your approach work if the two tables have duplicates within its own table as well as between the two tables which is my situation? I may not have any other choice.
    You know it's time to diet when you push away from the table and the table moves.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union and Duplicates (2002)

    I thought that you wanted to find out which records were duplicates between the tables. A join on two tables will clearly not locate duplicates within the tables.

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Union and Duplicates (2002)

    Sorry Hans. I should have mention that that was another reason I was using a union. I guess I'm stuck using a union and then finding the duplicates within that.

    I would hate to have to create a table from the union to speed it up because any updates would not get back to the original tables without writing an update query to run at a later time.
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union and Duplicates (2002)

    Would it be possible to merge the tables permanently?

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Union and Duplicates (2002)

    That's a good suggestion. It's either that or make a temporary table and work my way back with any updates. It might be a good reason to convince my user to combine the two tables. Now might be the time to do that. Thanks for the help.
    You know it's time to diet when you push away from the table and the table moves.

Posting Permissions

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