Case Sensisitve Find Duplicates Query

[Cliff]

I inherited a table with a few simple columns.
I want to find all records where the [Patient ID Medical Record Number] are the same AND the [Antibody, Antigen or Special Instruction Code] are the same.
The problem is that the [Antibody, Antigen or Special Instruction Code] needs to be case sensitive. For instance, a c (lower case) and C (upper case) are completely different results for us.

I used the query wizard and got this query, but it gives me a non-case sensitive result.

SELECT First(PTNT_INSTR.[Patient ID Medical Record Number]) AS [Patient ID Medical Record Number Field], First(PTNT_INSTR.[Antibody, Antigen or Special Instruction Code]) AS [Antibody, Antigen or Special Instruction Code Field], Count(PTNT_INSTR.[Patient ID Medical Record Number]) AS NumberOfDups
FROM PTNT_INSTR
GROUP BY PTNT_INSTR.[Patient ID Medical Record Number], PTNT_INSTR.[Antibody, Antigen or Special Instruction Code]
HAVING (((Count(PTNT_INSTR.[Patient ID Medical Record Number]))>1) AND ((Count(PTNT_INSTR.[Antibody, Antigen or Special Instruction Code]))>1));

Your help would be greatly appreciated.

 

[ByteMyzer]

Try:

SELECT   PTNT_INSTR.[Patient ID Medical Record Number],
         First(PTNT_INSTR.[Antibody, Antigen or Special Instruction Code]
                       AS [Antibody, Antigen or Special Instruction Code],
         Count(*)
FROM PTNT_INSTR
GROUP BY PTNT_INSTR.[Patient ID Medical Record Number],
         StrConv(PTNT_INSTR.[Antibody, Antigen or Special Instruction Code],128)
HAVING   Count(*) > 1;

 

[Cliff]

Thank you for your quick reply, and I apologize for my delayed response.
I got an error message when I tried this query.

 

[DCrake]

I tried this

SELECT CodeX, Count(1) AS Cnt
FROM TblCodes
GROUP BY CodeX, StrConv(CodeX,128)
WHERE Cnt > 1;

The data used

ABC123
abc123
aBc123
ABC123

Results
ABC123 = 2

 

[ByteMyzer]

Thank you for your quick reply, and I apologize for my delayed response.
I got an error message when I tried this query.
View attachment 25928

Try this corrected SQL statement:

SELECT   PTNT_INSTR.[Patient ID Medical Record Number],
         First(PTNT_INSTR.[Antibody, Antigen or Special Instruction Code][B][COLOR="Red"])[/COLOR][/B]
                       AS [Antibody, Antigen or Special Instruction Code],
         Count(*)
FROM PTNT_INSTR
GROUP BY PTNT_INSTR.[Patient ID Medical Record Number],
         StrConv(PTNT_INSTR.[Antibody, Antigen or Special Instruction Code],128)
HAVING   Count(*) > 1;

 

[Dave_epic]

If you convert the columns to hex using the code below you can run a match query on the two hex columns.

Add the following code to a module



Function StrToHex(S As Variant) As Variant
'
' Converts a string to a series of hexadecimal digits.
' For example, StrToHex(Chr(9) & "A~") returns 09417E.
'
Dim Temp As String, I As Integer
If VarType(S) <> 8 Then
StrToHex = S
Else
Temp = ""
For I = 1 To Len(S)

Temp = Temp & Hex(Asc(Mid(S, I, 1)))
Next I
StrToHex = Temp
End If
End Function





Call the module by typing the function below into the last window of a query (design view)


Expr1: StrToHex([Field1])

Click ascending for the sort on this field, which has been changed into HEX code, and run it.

 

[Cliff]

Thank you again for the quick reply.
I am getting a different error now, but suspect it may be related to my data.
"Reserved error (-1524); there is no message for this error."

Some of the fields also have a number in them, for example, I have Fy3, Fy4, A1, A2.
Are the number causing the error? If yes, can this be overcome?

 

[ByteMyzer]

This means that you have corrupted record(s) in the table. Try a compact and repair of the database.

 

[Cliff]

This means that you have corrupted record(s) in the table. Try a compact and repair of the database.

Thanks, this is killing me.

There were just over 250,000 records.
I deleted all but 20 of them.
Removed all of the columns I wasn't looking at.
Compacted and repaired and I still get this error.

I tried creating a whole new table with a new name and entered only a few records, and I still get this error.