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:
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.
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?!?!?!
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.