Duplicate Query - Case Sensitive

[rosef]

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]

Hi. Are you trying to discover if there are duplicate entries or trying to prevent one from happening?

 

[rosef]

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]

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]

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]

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:

Online MD5 Hash Generator & SHA1 Hash Generator

Generate the MD5 and SHA1 checksum for any file or string in your browser without uploading it, quickly and efficiently, no software installation required.

onlinemd5.com

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]

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.

Access Cookbook

Create a Query That Uses Case-Sensitive Criteria Problem You have a table of words, some of which appear multiple times. Each instance of these words is spelled using a different … - Selection from Access Cookbook [Book]

www.oreilly.com

 

[The_Doc_Man]

You can also call the StrComp function with case sensitivity enabled to determine exact-case match.

 

[rosef]

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 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.