Results 1 to 7 of 7

Thread: Merging records

  1. #1
    Join Date
    Mar 2004
    Posts
    41

    Unanswered: Merging records

    Check out the attached csv...

    I am faced with a total of over 17000 duplicate records, most of which are similar to the attachment. In the process of assembling info, we had to pull from several sources, not all of which provided the same fields. Going back and starting the process over is not an option - we've been at this for 6 weeks.

    How can I get these records to merge? If you look real close, I can copy the Desc to the fourth record and delete the first three. The goal is to end up with only one record.

    Or maybe there's an easier way? Remember that these records don't always appear in this order.. and that other duplicated information exists in the table. This is just the most common situation at the moment.

    Note: the second attachment is a more complex version of the problem, and should result in two records. Same problem, though - need to merge the lines properly.
    Attached Files Attached Files
    Last edited by kcary; 07-08-04 at 11:42.

  2. #2
    Join Date
    Jun 2004
    Posts
    16
    This should take less than 6 weeks.

    Import your sample table into Access

    Then create a table with a list of unique PackNo's ( A grouping query will do this for you, or do a search on the word "Distinct")

    Then you simply design an update query for each field, updating from fields that are not null. Design a query for each field and join them by the pack no.

    Below is the SQL statement for the "Description Field"

    UPDATE Sample INNER JOIN YourTableName ON Sample.PackNo = YourTableName.PackNo SET YourTableName.Description = sample.description
    WHERE (((Sample.Description) Is Not Null));
    Last edited by David21495; 07-08-04 at 16:35.

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    not to bardge in, but you can put SQL in the description field in design view?

  4. #4
    Join Date
    Jun 2004
    Posts
    16
    No, you go into SQL view and paste it there. "Description" was the name of one of his fields.
    Last edited by David21495; 07-08-04 at 17:30.

  5. #5
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Oh ok, I got confused for a minute

  6. #6
    Join Date
    Mar 2004
    Posts
    41
    Only one problem - the PackNo field is not unique. Past items may be reassigned that number (see sample 2). Can this be done using the PackNo and Description fields as the unique identifier.

  7. #7
    Join Date
    Jun 2004
    Posts
    16
    Absolutely, just join both fields in your update query. Below is the SQL updating the Vendor from Sample2 to tblYourTable:

    UPDATE YourTable INNER JOIN Sample2 ON (YourTable.PackNo = Sample2.PackNo) AND (YourTable.Description = Sample2.Description) SET YourTable.Vendor = Sample2.Vendor
    WHERE (((Sample2.Vendor) Is Not Null));
    Last edited by David21495; 07-09-04 at 20:31.

Posting Permissions

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