Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: Dedupping Memo fields HELP!- Access 2007

    Hello,

    I'm trying to remove duplicates from a memo field within a larger table that contains vehicle claim information. I'm trying to do this since the original data we pull has many duplicate values in the comments and no other value is unique across the board. The table is roughly laid out as such:

    PrimaryKey, Case#, Date, Reason_Code, Comments(Memo), OtherFields

    Obviously Access will not dedupe a memo field so I'm stuck with breaking it into text fields and using DISTINCT in a query. Long story short, is there a way to deduplicate a memo field easily while retaining another unique identifier within the table (such as the primary key)? Even if I break it up, I still need to retain a link back to the original table since in the end all of the unique info needs to be displayed on a form and I'm constrained by the 2000 character limit for records outside of memos.

    Currently here's my method:
    1) Break memo into text fields and SELECT DISTINCT (omitting primary key) (in my example I get 1677 unique values)
    2) Once deduped, re-referencing the new table back into the original table using a text compare between the TXT portions and sections of the original Memo field (a terible way to do it, I know...I get 1675 values...I lose 2)

    I can't lose any...thus my impasse. Any and all help is much appreciated.

    Thanks!

    -Chris

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I don't envy you! Unfortunately I think your approach is correct. I have had to do something similar and that is the way I did it. The only comment I have is about the two record difference. Do you have any nulls in your unique results? If you do, when you try to match them up, you won't get a match because Null does not equal Null?!?!?!

  3. #3
    Join Date
    Oct 2009
    Posts
    2
    Yeah...I do have a couple. Is tehre some way to get around this? The crappy part about it is that I need the records because even though the comments are blank, there is other info that needs to be included.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can update the Nulls to Empty Strings ("").

Posting Permissions

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