Duplicate Query - Case Sensitive (1 Viewer)

rosef

New member
Local time
Today, 08:42
Joined
Jan 18, 2021
Messages
9
I have a table that has a ID field that is case sensitive. I am trying to run a duplicate query on the ID field but it is not taking in account upper and lower case? Is there a way in a simple query to check for duplicates based on upper and lower case?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you trying to discover if there are duplicate entries or trying to prevent one from happening?
 

rosef

New member
Local time
Today, 08:42
Joined
Jan 18, 2021
Messages
9
Hi. Are you trying to discover if there are duplicate entries or trying to prevent one from happening?
I am trying to discover the duplicate entries.
 

isladogs

MVP / VIP
Local time
Today, 15:42
Joined
Jan 14, 2017
Messages
18,186
OK just to clarify, by default Access will treat e.g. Apple, APPLE, apple, aPPle & aPpLE as duplicates.
But am I correct in thinking you would like to treat all of those as non-dupes
 

rosef

New member
Local time
Today, 08:42
Joined
Jan 18, 2021
Messages
9
When I do a duplicate query in Access using the ID field it is showing the ones below as duplicates and I don't want them to be shown as duplicates.
0013h00000HFbsa and 0013h00000HFbsA
The "A" on the end one is upper case and one is lower case. They should not be duplicates. I have a lot of these.
Here is the SQL View:
SELECT TestTable.[AccountID], TestTable.[GMID], TestTable.[ContactID], TestTable.[Customer Name]
FROM TestTable
WHERE (((TestTable.[AccountID]) In (SELECT [AccountID] FROM [TestTable] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY TestTable.[AccountID];
 

plog

Banishment Pending
Local time
Today, 10:42
Joined
May 11, 2011
Messages
11,611
Is there a way in a simple query to check for duplicates based on upper and lower case?

No. There is a way though--hashing. Basically you have a function that transforms one unique string into another. Check out this site:


In the second box type in "Test" and it yields "0CBC6611F5540BD0809A388DC95A615B"
Then change it to "test" and it yields "098F6BCD4621D373CADE4E832627B4F6"

If you first run your strings through a hash it will convert your strings such that Access GROUP BY will no longer identify them as the same string.
I am certain someone has built a hashing function (MD5, SHA1, etc.) for Access you can google and find. Or even build your own.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:42
Joined
Feb 19, 2002
Messages
42,970
Jet/ACE is not case sensitive and I have never seen an option to tell it to be case sensitive but that doesn't mean that one doesn't exist. I don't know how it will work if your data is in SQL Server which I believe can be defined as case sensitive. There is a VBA option that might be helpful. Here is a link to an explanation of how to create a function.

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 28, 2001
Messages
26,999
You can also call the StrComp function with case sensitivity enabled to determine exact-case match.
 

rosef

New member
Local time
Today, 08:42
Joined
Jan 18, 2021
Messages
9
You can also call the StrComp function with case sensitivity enabled to determine exact-case match.
I thought the StrComp function compares to fields. I just want to compare on field with case sensitivity.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:42
Joined
Oct 29, 2018
Messages
21,358
I thought the StrComp function compares to fields. I just want to compare on field with case sensitivity.
Hi. It might still be possible. Can you please post a sample db with some test data? Thanks.
 

Users who are viewing this thread

Top Bottom