Removing duplicates from a table that contains upper and lower case

[badger8]

I have records within a table that I know have duplicates.

The problem is I may have two records that are identical except one is in upper case and the lower case.

I've been just doing is the following:

1. I make a copy of my table (structure only)
2. I set Frist name, Last name, city, and postal the primary keys.
3. I create an append query from the orginal to this copy.

But the records that are similiar, but different cases aren't beging removed.

I really need to get some help in the best way to accomplish my task.

PS.. I do not have at the time Find Duplicate wizard, so I need another solution.

Thanks
Ray

 

[dumdum]

How about creating a "grouped" query and then appending that data to the new table? The grouping should "consolidate" the matching records. I am not sure if a grouped query exports/appends like that but you could give it a try.

 

[THoey]

Badger8, did you try adding the UCASE function to your query? That should make all the letters upper case and then it should kick out the dupes... Terry M. Hoey

 

[smedvid]

This is a good reason to force users' to enter only Upper case values... just a thought... Steve Medvid
"IT Consultant & Web Master"

 

[badger8]

Thanks for the solutions.

I've noticed that sometimes when I do my dedupes like the way I mentioned above, Access will remove one of the records, but somethimes it doesn't.

Do you know why this may happen?