Need a Case-Sensitive Group By Totals Query - Is there a Workaroun

[jgraves]

I am using Access 2007.

I am creating a group by totals query to identify all unique values in a
column of a table. (A "Group By")
I need it to be case-sensitive - I am aware that Access 2007 does not
consider case in its queries. Does anyone know of any workarounds that would
fit?

Here is an example, I need a list like this:
Acme Brand
Acme Brand
Acme Brand
ACME Brand
ACME BRAND
ACME BRAND

To return this result:
Acme Brand
ACME Brand
ACME BRAND

I know the lack of case-sensitivity is usually beneficial, but in this case,
our client needs to have a list of all unique values.

Any help or suggestions greatly appreciated.

Jen G

 

[John Spencer]

Nothing comes to mind if the sets of data are really large. But you might try
using a function to test the case of the letters and build a string based on
that.

UNTESTED VBA function follows

Public Function fGroupByCaseString(strIn)
Dim strReturn as Variant
Dim i as Long

If Len(strIn & vbNullString) = 0 Then
strReturn = strIn
Else
For I = 1 to Len(strIn)
If Asc(Mid(strIn,I,1))>90 THEN
strReturn = strReturn & "L"
Else
strReturn = strReturn & "U"
End If
Next I
End if
fGroupByCaseString = strReturn
End function

Then in your query
SELECT CompanyName
FROM SomeTable
GROUP BY CompanyName, fGroupByCaseString(CompanyName)

The above works as long as you stick to the unaccented letters, etc.
If you do have them then change the test to

If StrComp(Mid(StrIn,I,1),LCase(Mid(StrIn,I,1)),0)= 0 Then ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale_Fye via AccessMonster.com]

Jen,

I've got a function I use for string comparisons that changes the values of
individual elements within the string. It converts all caps to other
characters (by subtracting 26 from the ascii value of the character). I can
then write a query like:

SELECT [SomeField]
FROM tbl_TextCompare
GROUP BY [SomeField], fnCaseCompare([SomeField]);

The other nice thing about this function, is that it gives me a way of
querying on a particular value, like:

SELECT tbl_TextCompare.SomeField
FROM tbl_TextCompare
WHERE fnCaseCompare([SomeField])=fnCaseCompare("ACME BRAND")

Public Function fnCaseCompare(TextToConvert As Variant) As Variant

Dim intLoop As Integer
Dim strChar As String

If IsNull(TextToConvert) Or Len(TextToConvert) = 0 Then
fnCaseCompare = Null
Exit Function
End If

For intLoop = 1 To Len(TextToConvert)
strChar = Mid(TextToConvert, intLoop, 1)
Select Case Asc(strChar)
Case 65 To 90
fnCaseCompare = fnCaseCompare & Chr$(Asc(strChar) - 26)
Case Else
fnCaseCompare = fnCaseCompare & strChar
End Select
Next

End Function

 

[Dale Fye]

Actually, I realized that this version of the function converts empty string
to NULLs, so you might want to change the line that reads:

fnCaseCompare = NULL

to

fnCaseCompare = TextToConvert

----
Dale

Jen,

I've got a function I use for string comparisons that changes the values of
individual elements within the string. It converts all caps to other
characters (by subtracting 26 from the ascii value of the character). I can
then write a query like:

SELECT [SomeField]
FROM tbl_TextCompare
GROUP BY [SomeField], fnCaseCompare([SomeField]);

The other nice thing about this function, is that it gives me a way of
querying on a particular value, like:

SELECT tbl_TextCompare.SomeField
FROM tbl_TextCompare
WHERE fnCaseCompare([SomeField])=fnCaseCompare("ACME BRAND")

Public Function fnCaseCompare(TextToConvert As Variant) As Variant

Dim intLoop As Integer
Dim strChar As String

If IsNull(TextToConvert) Or Len(TextToConvert) = 0 Then
fnCaseCompare = Null
Exit Function
End If

For intLoop = 1 To Len(TextToConvert)
strChar = Mid(TextToConvert, intLoop, 1)
Select Case Asc(strChar)
Case 65 To 90
fnCaseCompare = fnCaseCompare & Chr$(Asc(strChar) - 26)
Case Else
fnCaseCompare = fnCaseCompare & strChar
End Select
Next

End Function

I am using Access 2007.

I am creating a group by totals query to identify all unique values in a
column of a table. (A "Group By")
I need it to be case-sensitive - I am aware that Access 2007 does not
consider case in its queries. Does anyone know of any workarounds that would
fit?

Here is an example, I need a list like this:
Acme Brand
Acme Brand
Acme Brand
ACME Brand
ACME BRAND
ACME BRAND

To return this result:
Acme Brand
ACME Brand
ACME BRAND

I know the lack of case-sensitivity is usually beneficial, but in this case,
our client needs to have a list of all unique values.

Any help or suggestions greatly appreciated.

Jen G

 

[Clifford Bass]

Hi Jen,

You might try this, adjusted for your situation:

tblCaseDifferentiating
TheKey - Primary key
Symbol - A string column with varyingly mixed case data (i.e. ABC, ABc,
Abc, abc, etc.)

SELECT A.TheKey, A.Symbol
FROM tblCaseDifferentiating AS A
WHERE (((Not Exists (select * from tblCaseDifferentiating as B where
B.TheKey < A.TheKey and StrComp(B.Symbol, A.Symbol, 0) = 0))=True))
ORDER BY A.Symbol;

Clifford Bass

 

[Clifford Bass]

Hi Jen,

Further information: Inclusion of your primary key field is important
if you need to do grouping. In which case group by both TheKey AND Symbol.

Clifford Bass