Case Sensitive Query

[carl]

Hi.

An extract of my data table looks as so:

SYMBOL Code
AA AE
AA ae
AAPL AB
ABT A2
ACE 7G

I am usubg these 2 queries:

Q1:

SELECT DISTINCT SYMBOL, [GROUP NAME]
FROM Temp;


Q2:

SELECT DISTINCT SYMBOL, Count([GROUP NAME]) AS UniqueCount
FROM Q1
GROUP BY SYMBOL;

Which produces the following result:

SYMBOL UniqueCountOfCode
A 1
AA 1
AAI 1
AAP 1
AAPL 1
AAUK 1


The query should produce a Unique Count for Symbol "AA" of 2. For some
reason the query does not differentiate between "AE" and "ae".

Is there a way to modify the query so it does ?

Thank you in advance.

 

[Stefan Hoffmann]

hi Carl,

SYMBOL Code
AA AE
AA ae
AAPL AB
ABT A2
ACE 7G

The query should produce a Unique Count for Symbol "AA" of 2. For some
reason the query does not differentiate between "AE" and "ae".

Is there a way to modify the query so it does ?

Based on

http://support.microsoft.com/kb/209674

I would use a function like

Public Function AscStr(AValue As Variant) As String

Dim Count As Long
Dim Result As String

If IsNull(AValue) Then
Result = ""
Else
For Count = 1 To Len(AValue)
Result = Result & "-" & Asc$(Mid$(AValue, Count, 1))
End If
End If

AscStr = Result

End Function

and

SELECT Symbol, Count(AscStr(AValue))
FROM yourTable
GROUP BY Symbol

This is completly untested, but I'm sure you will get the idea.


mfG
--> stefan <--

 

[carl]

Thank you very much. I tried to follow the example in the link but do not
understand how to incorporate the modification into a query that will give
the correct result.

Would you mind showing me the query that can give me a unique count of
"Code" per Symbol ?

Hi.

An extract of my data table looks as so:

SYMBOL Code
AA AE
AA ae
AAPL AB
ABT A2
ACE 7G

I am usubg these 2 queries:

Q1:

SELECT DISTINCT SYMBOL, [GROUP NAME]
FROM Temp;


Q2:

SELECT DISTINCT SYMBOL, Count([GROUP NAME]) AS UniqueCount
FROM Q1
GROUP BY SYMBOL;

Which produces the following result:

SYMBOL UniqueCountOfCode
A 1
AA 1
AAI 1
AAP 1
AAPL 1
AAUK 1


The query should produce a Unique Count for Symbol "AA" of 2. For some
reason the query does not differentiate between "AE" and "ae".

Is there a way to modify the query so it does ?

Thank you in advance.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

http://office.microsoft.com/en-us/access/HA100627601033.aspx

The "Temp = Temp" line needs to be changed to this (all one line):

Temp = Temp & String(2 -
Len(Hex(Asc(Mid(S, i, 1)))), "0") & Hex(Asc(Mid(S, i, 1)))


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSk1BTYechKqOuFEgEQKI3wCcDtVO5QrM8gR9IKBWrVqQQJBlgDcAoIHG
xgRDa8bDY9gS1l0Yj+LMlOTT
=8G7K
-----END PGP SIGNATURE-----