How to perform case sensitive group by in MS Access

Question

In MS Access database to find the mismatch (i.e. difference) records between two tables(Employee and Employee_PROD) I am using the UNION ALL. The query is as follows:

SELECT [COMPANY],[DEPT],[DOJ],[EMP_ID],[Name],[SUB_COMPANY] FROM 

(SELECT '[Employee]' AS TableName,[COMPANY],[DEPT],[DOJ],[EMP_ID],[Name],[SUB_COMPANY] FROM [Employee] 

UNION ALL 

SELECT '[Employee_PROD]' AS TableName,[COMPANY],[DEPT],[DOJ],[EMP_ID],[Name],[SUB_COMPANY] FROM [Employee_PROD] ) 

GROUP BY [COMPANY],[DEPT],[DOJ],[EMP_ID],[Name],[SUB_COMPANY] 
HAVING COUNT(*) = 1 AND MIN(TableName) = '[Employee]'

The problem I am facing is that the GROUP BY is not considering the case sensitivity. For example "andrew" and "Andrew" is treated as same. I want to perform group by with case sensitive to find the difference records.

Is there any way to do the same in MS Access?

Is there any other approach to find the differences between two tables having same column names, data types and the number of records is 7,00, 000?

I have tried the following:

Load data to DataTable and then find the difference. Got out of memory exception due to huge amount of data.
Use NOT EXISTS to compare the rows. The query got hanged and the execution never completed.
The UNION ALL approach is working but the issue is that GROUP BY is not considering the case sensitivity.

I am executing the query in MS Access database via C# OleDbConnection. — Dukhabandhu Sahoo, Dec 06 '17 at 10:11
If so, I'm not sure, you can call VBA functions in Access. But you could pick the ASCII value of the first character, if that is what you care about. For more subsequent characters you might expand that using MID. — Gustav, Dec 06 '17 at 11:59

Gustav · Answer 1 · 2017-12-06T11:56:59.060

0

You could group by the byte value:

? StrToByte("Andrew")
416E64726577
? StrToByte("andrew")
616E64726577

though it might be a bit slow with the large amount of data you have.

Public Function StrToByte(ByVal strChars As String) As String

  Dim abytChar()  As Byte
  Dim lngChar     As Long
  Dim strByte     As String

  abytChar() = StrConv(strChars, vbFromUnicode)

  strByte = Space(2 * (1 + UBound(abytChar) - LBound(abytChar)))
  For lngChar = LBound(abytChar) To UBound(abytChar)
    Mid(strByte, 1 + 2 * lngChar) = Hex(abytChar(lngChar))
  Next

  StrToByte = strByte

End Function

For SQL only, and if you only about the first character, try:

GROUP BY [COMPANY],[DEPT],[DOJ],[EMP_ID],[Name], ASC([Name]),[SUB_COMPANY]

edited Dec 06 '17 at 11:56

answered Dec 06 '17 at 07:30

Gustav

53,498
7
29
55

Since it's from C# (assumption since the question is tagged C#), you probably can't use UDFs – Erik A Dec 06 '17 at 07:33
I noticed, but: _Is there any way to do the same in MS Access?_ So? – Gustav Dec 06 '17 at 07:42
As said, it's an assumption. If it's actually just access, the C# tag should be removed. – Erik A Dec 06 '17 at 07:44

score 0 · Answer 2 · answered Dec 06 '17 at 12:35

0

Can you not specify OPTION COMPARE BINARY in a module to perform a case sensitive search?

This thread https://access-programmers.co.uk/forums/showthread.php?t=33962 and this one How to write Case Sensitive Query for MS Access? both describe different methods.

answered Dec 06 '17 at 12:35

Minty

1,616
1
8
13

I don't want to perform the search action but the group by query. – Dukhabandhu Sahoo Dec 06 '17 at 13:04
Apologies I missed that subtle difference in the question - this might assist https://support.microsoft.com/en-gb/help/244693/how-to-perform-a-case-sensitive-join-through-microsoft-jet – Minty Dec 06 '17 at 16:12

How to perform case sensitive group by in MS Access

2 Answers2