Results 1 to 12 of 12
  1. #1
    Join Date
    May 2007
    Posts
    6

    Unanswered: Combining similar records

    My inherited database contains multiple records for the same product. In some instance I have counted up to four records for an individual product. The difference in their descriptions range from minute to significant. I would like to select and combine these into one record while maintaining their sales history located in another table.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How would you determine which product record to keep?
    Code:
    1	Spanner		8mm	£2.50
    2	Spanner		8cm	£2.52
    3	Spannner	8mm	£2.50
    And you're more than likely going to hit some big problems with your sale history (assuming that your product ID (primary key) is the foreign key in your sales history table.
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    I have had several similar problems to this, and I know you don't want to hear this, but the only way I have accurately (to my satisfaction) been able to clean things up is to do it manually, a record at a time. I have seen posted some search forms that use "fuzzy" logic and that may help you, but I wasn't sold enough to use it myself.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's always the problem - if there are no set rules then mistakes will be made.
    If it's a simple case of:
    Code:
    1	Spanner		8mm	£2.50
    2	Spanner			
    3			8mm	£2.50
    It can be combined into one record much easier - but what identifier do you keep? 1, 2 or 3!!

    As bd mentioned above - manual entry is the only way t have this as accurate as you'd like it. You can write a query to pick out the dupes easily enough which will cut down your workload significantly, but that's about it.
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I agree that manual conversion is going to be the only viable option. And "merging the descriptions" is only half the problem here; maintaining the related sales history is going to be thr real bear, I fear!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    More than agreed!
    George
    Home | Blog

  7. #7
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Hopefully, we haven't ruined your day/week/month! And, hopefully, you get paid by the hour!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by bdb04
    ...hopefully, you get paid by the hour!
    Haha, that bit made me giggle
    George
    Home | Blog

  9. #9
    Join Date
    May 2007
    Posts
    6
    I wish I were paid by the hour because I would be rather well off. My preference is to combine them manually so I can control what is being combined since the descriptions vary widely. The example provided by georgev effectively shows what I'm dealing with. Choosing which one to keep is simple as I will base it on sales history as the duplicates tend to have fewer and less recent transactions. In this example I would choose to keep #1. How would I combine the other items? What problems will I encounter in maintaining the transaction history?


    Quote Originally Posted by georgev
    It's always the problem - if there are no set rules then mistakes will be made.
    If it's a simple case of:
    Code:
    1	Spanner		8mm	2.50
    2	Spanner			
    3			8mm	2.50

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    With the above example you say we'd keep product #1;
    Product 1 and 3 have 10 sales history records while products 2 has 12.
    Now, do you combine these by changing the foreign key of productId to 1 for all these? Or do you simply keep product 2 because it has more? Either way you'll have to somehow make these match with the records you are left iwth in the products table.
    George
    Home | Blog

  11. #11
    Join Date
    May 2007
    Posts
    6
    In most cases I can easily identify the one I want to keep. When I can't I will just select one, combine the others and alter the description. It may not be proper to do it that way but it can be very difficult finding what I need at times. I just have not been able to find how to accomplish it without losing any transaction history.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    1	Spanner		8mm	£2.50
    2	Spanner			
    3			8mm	£2.50
    So we pick number 1, correct?
    Code:
    UPDATE TransactionHistory
    SET OurForeignKey = 1
    WHERE OurForeignKey IN (2,3)
    The IN function is the same as saying
    Code:
    WHERE OurForeignKey = 2
    OR OutForeignKey = 3
    hth
    George
    Home | Blog

Posting Permissions

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