Case Sensitive Query

[carl]

I posted this question a couple weeks ago and want to thank you for offering
your help. I tried to follow the replies but still cannot get a quesry to do
what I need.

Thus I've reposted.

I need a query that will produce a Unique Count of "Code" for each "Symbol".

My problem is that my "Code" can be upper and lower case so my query needs
to be case sensitive.

An extract from my database looks like this:

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


I need a query that will produce this:

SYMBOL UniqueCountCode
A 1
AA 2
AAI 1
AAP 1
AAPL 1

Thank you in advance.

 

[KARL DEWEY]

Your narrative does not match your example. You have a count of 2 for AA but
the Code for them have different case and therefore should not be 2 unless I
am missing something.

If your Code always has only 2 characters then you can build a calculated
field representing the Code that separates by case.
CaseCode: Asc(Left(

,1)) & Asc(Right([Code],1))
Ae will give - 65101
AE will give - 6569

 

[carl]

Thank you. For AA, there are 2 codes - AE and ae. I would like the query to
see these 2 unique codes. Does that make sense ?

Your narrative does not match your example. You have a count of 2 for AA but
the Code for them have different case and therefore should not be 2 unless I
am missing something.

If your Code always has only 2 characters then you can build a calculated
field representing the Code that separates by case.
CaseCode: Asc(Left(

,1)) & Asc(Right([Code],1))
Ae will give - 65101
AE will give - 6569

--
Build a little, test a little.


[QUOTE="carl"]
I posted this question a couple weeks ago and want to thank you for offering
your help. I tried to follow the replies but still cannot get a quesry to do
what I need.

Thus I've reposted.

I need a query that will produce a Unique Count of "Code" for each "Symbol".

My problem is that my "Code" can be upper and lower case so my query needs
to be case sensitive.

An extract from my database looks like this:

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


I need a query that will produce this:

SYMBOL	UniqueCountCode
A	1
AA	2
AAI	1
AAP	1
AAPL	1

Thank you in advance.
[/QUOTE][/QUOTE]

 

[KARL DEWEY]

Try this --
SELECT [SYMBOL], Count(Asc(Left(

,1)) & Asc(Right([Code],1))) AS
CountCaseCode
FROM YourTable
GROUP BY [SYMBOL];

--
Build a little, test a little.


[QUOTE="carl"]
Thank you. For AA, there are 2 codes - AE and ae. I would like the query to
see these 2 unique codes. Does that make sense ?

[QUOTE="KARL DEWEY"]
Your narrative does not match your example.  You have a count of 2 for AA but
the Code for them have different case and therefore should not be 2 unless I
am missing something.

If your Code always has only 2 characters then you can build a calculated
field representing the Code that separates by case.
CaseCode:  Asc(Left([Code],1)) & Asc(Right([Code],1))
Ae will give - 65101
AE will give - 6569

--
Build a little, test a little.


[QUOTE="carl"]
I posted this question a couple weeks ago and want to thank you for offering
your help. I tried to follow the replies but still cannot get a quesry to do
what I need.

Thus I've reposted.

I need a query that will produce a Unique Count of "Code" for each "Symbol".

My problem is that my "Code" can be upper and lower case so my query needs
to be case sensitive.

An extract from my database looks like this:

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


I need a query that will produce this:

SYMBOL	UniqueCountCode
A	1
AA	2
AAI	1
AAP	1
AAPL	1

Thank you in advance.
[/QUOTE][/QUOTE][/QUOTE]

 

[Clifford Bass]

Hi Carl,

Nice question!

Karl's solution is a good one if you only have two characters and for a
two-character solution, is similar to what I might have suggested.

However, if you have more characters than two, or if the number of
characters can vary, you quickly run into other issues. Here is something to
try that allows for varying lengths. Since you did not provide complete
information about your table, I am using "tblCaseDifferentiating" for the
name. It also has a primary key named "TheKey". This provides a very
important part for determining what values are actually different in terms of
case only. Here is your SQL (you will have to adjust for your table and key
field names):

SELECT A.TheKey, A.Symbol, A.Code, Count(*) AS TheCount
FROM tblCaseDifferentiating AS A, tblCaseDifferentiating AS C
WHERE (((Not Exists (select * from tblCaseDifferentiating as B where
B.TheKey < A.TheKey and StrComp(B.Symbol, A.Symbol, 0) = 0 and
StrComp(B.Code, A.Code, 0) = 0))=True) AND
((StrComp([C].[Symbol],[A].[Symbol],0))=0) AND
((StrComp([C].

,[A].[Code],0))=0))
GROUP BY A.TheKey, A.Symbol, A.Code
ORDER BY A.Symbol, A.Code;

This assumes that all of your Symbol values may vary in their case
also, and that it is also important.

Hope that helps,

Clifford Bass

 

[Clifford Bass]

Hi Carl,

Oops, did not quite answer that correctly. Will post back shortly.

Clifford Bass

 

[Clifford Bass]

Hi Carl,

Okay, here it is. Name this first query "qryUniqueSymbolsCodes":

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

Then use this query:

SELECT qryUniqueSymbolsCodes.A.TheKey, qryUniqueSymbolsCodes.Symbol,
Count(*) AS CodeCount
FROM qryUniqueSymbolsCodes
GROUP BY qryUniqueSymbolsCodes.A.TheKey, qryUniqueSymbolsCodes.Symbol
ORDER BY qryUniqueSymbolsCodes.Symbol;

Clifford Bass

 

[Clifford Bass]

Hi Carl,

Or all in one:

SELECT A.TheKey, A.Symbol, Count(*) AS CodeCount
FROM tblCaseDifferentiating AS A, tblCaseDifferentiating AS C
WHERE (((Not Exists (select * from tblCaseDifferentiating as B where
B.TheKey < A.TheKey and StrComp(B.Symbol, A.Symbol, 0) = 0))=True) AND
((StrComp([C].[Symbol],[A].[Symbol],0))=0) AND ((Not Exists (select * from
tblCaseDifferentiating as D where StrComp(D.Symbol, A.Symbol, 0) = 0 and
D.TheKey < C.TheKey and StrComp(D.Code, C.Code, 0) = 0))=True))
GROUP BY A.TheKey, A.Symbol
ORDER BY A.Symbol;

Guess I am being obsessive.

Clifford Bass