Case sensetive query

[Ajay]

Hi
I am trying to do the following.
I have a set of records
Example
1 Test
2 test
3 TEST
4 ACCESS
5 EXCEL

If I'm running a query with a count I am getting the following result
record number of records
test 3
access 1
excel 1


My question is how can I make the query casesensetive so that my
query output
will be
record number of records
Test 1
test 1
TEST 1
Access 1
Excel 1

This means that the query shouldn't count test as one record but as 3
seperate records because there is a difference in the upper and lower
cases.

Thanks in advance

Ajay

[Lyle Fairfield]

Re: Case sensetive query

ajaybrdja@yahoo .com (Ajay) wrote in
news:49836dd.03 10200750.794e02 eb@posting.goog le.com:
[color=blue]
> Hi
> I am trying to do the following.
> I have a set of records
> Example
> 1 Test
> 2 test
> 3 TEST
> 4 ACCESS
> 5 EXCEL
>
> If I'm running a query with a count I am getting the following result
> record number of records
> test 3
> access 1
> excel 1
>
>
> My question is how can I make the query casesensetive so that my
> query output
> will be
> record number of records
> Test 1
> test 1
> TEST 1
> Access 1
> Excel 1
>
> This means that the query shouldn't count test as one record but as 3
> seperate records because there is a difference in the upper and lower
> cases.[/color]

You might TRY (after backing up your table) changing the field type to
Binary.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

Re: Case sensetive query

Lyle Fairfield <MissingAddress @Invalid.Com> wrote in
news:Xns941A7EE 606071FFDBA@130 .133.1.4:
[color=blue]
> ajaybrdja@yahoo .com (Ajay) wrote in
> news:49836dd.03 10200750.794e02 eb@posting.goog le.com:
>[color=green]
>> Hi
>> I am trying to do the following.
>> I have a set of records
>> Example
>> 1 Test
>> 2 test
>> 3 TEST
>> 4 ACCESS
>> 5 EXCEL
>>
>> If I'm running a query with a count I am getting the following result
>> record number of records
>> test 3
>> access 1
>> excel 1
>>
>>
>> My question is how can I make the query casesensetive so that my
>> query output
>> will be
>> record number of records
>> Test 1
>> test 1
>> TEST 1
>> Access 1
>> Excel 1
>>
>> This means that the query shouldn't count test as one record but as 3
>> seperate records because there is a difference in the upper and lower
>> cases.[/color]
>
> You might TRY (after backing up your table) changing the field type to
> Binary.[/color]

If you are using XP you could use:

CurrentProject. Connection.Exec ute "ALTER TABLE tbl2002Transact ions ALTER
fldDescription Binary"

I think this will work for 2K as well.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Fletcher Arnold]

Re: Case sensetive query

"Ajay" <ajaybrdja@yaho o.com> wrote in message
news:49836dd.03 10200750.794e02 eb@posting.goog le.com...[color=blue]
> Hi
> I am trying to do the following.
> I have a set of records
> Example
> 1 Test
> 2 test
> 3 TEST
> 4 ACCESS
> 5 EXCEL
>
> If I'm running a query with a count I am getting the following result
> record number of records
> test 3
> access 1
> excel 1
>
>
> My question is how can I make the query casesensetive so that my
> query output
> will be
> record number of records
> Test 1
> test 1
> TEST 1
> Access 1
> Excel 1
>
> This means that the query shouldn't count test as one record but as 3
> seperate records because there is a difference in the upper and lower
> cases.
>
> Thanks in advance
>
> Ajay[/color]


As an alternative to Lyle's which does not involve changing data types you
could simply write a query. Asuming the data 'Test', 'test', 'TEST' etc is
held in a field called MyField in the table MyTable, then the query would
be:

SELECT MyField, Count(*) AS MyTotal
FROM MyTable
GROUP BY MyField, GetAscString([MyField])

You would simply have to paste the following function into a code module:

' *************** *************** *************** ********
Public Function GetAscString(va rInput) As String

Dim lngCount As Long
Dim strOut As String

If Len(varInput) > 0 Then
For lngCount = 1 To Len(varInput)
strOut = strOut & Asc(Mid$(varInp ut, lngCount, 1))
Next lngCount
End If

GetAscString = strOut

End Function

' *************** *************** *************** ********


HTH

Fletcher

[TC]

Re: Case sensetive query


"Fletcher Arnold" <fletch@home.co m> wrote in message
news:bn1pmk$nb3 $1@sparta.btint ernet.com...

(snip)
[color=blue]
> As an alternative to Lyle's which does not involve changing data types you
> could simply write a query. Asuming the data 'Test', 'test', 'TEST' etc[/color]
is[color=blue]
> held in a field called MyField in the table MyTable, then the query would
> be:
>
> SELECT MyField, Count(*) AS MyTotal
> FROM MyTable
> GROUP BY MyField, GetAscString([MyField])
>
> You would simply have to paste the following function into a code module:
>
> ' *************** *************** *************** ********
> Public Function GetAscString(va rInput) As String
>
> Dim lngCount As Long
> Dim strOut As String
>
> If Len(varInput) > 0 Then
> For lngCount = 1 To Len(varInput)
> strOut = strOut & Asc(Mid$(varInp ut, lngCount, 1))
> Next lngCount
> End If
>
> GetAscString = strOut
>
> End Function
>
> ' *************** *************** *************** ********[/color]


You would want to pad the Asc values to a fixed length, eg:
format(asc(...) , "000").

Otherwise, you don't know whether "12345" was 1, 23, 45 or: 123, 45 or: 1,
234, 5 etc. - so in principle, you could erroneously match those three
strings, as just one string.

I realize this might not happen with the actual numbers in question. But as
a matter of principle, it would be safer to do the fixed-length thing.

TC

[Fletcher Arnold]

Re: Case sensetive query

"TC" <a@b.c.d> wrote in message news:1066802481 .214681@teuthos ...[color=blue]
>
> "Fletcher Arnold" <fletch@home.co m> wrote in message
> news:bn1pmk$nb3 $1@sparta.btint ernet.com...
>
> (snip)
>[color=green]
> > As an alternative to Lyle's which does not involve changing data types[/color][/color]
you[color=blue][color=green]
> > could simply write a query. Asuming the data 'Test', 'test', 'TEST' etc[/color]
> is[color=green]
> > held in a field called MyField in the table MyTable, then the query[/color][/color]
would[color=blue][color=green]
> > be:
> >
> > SELECT MyField, Count(*) AS MyTotal
> > FROM MyTable
> > GROUP BY MyField, GetAscString([MyField])
> >
> > You would simply have to paste the following function into a code[/color][/color]
module:[color=blue][color=green]
> >
> > ' *************** *************** *************** ********
> > Public Function GetAscString(va rInput) As String
> >
> > Dim lngCount As Long
> > Dim strOut As String
> >
> > If Len(varInput) > 0 Then
> > For lngCount = 1 To Len(varInput)
> > strOut = strOut & Asc(Mid$(varInp ut, lngCount, 1))
> > Next lngCount
> > End If
> >
> > GetAscString = strOut
> >
> > End Function
> >
> > ' *************** *************** *************** ********[/color]
>
>
> You would want to pad the Asc values to a fixed length, eg:
> format(asc(...) , "000").
>
> Otherwise, you don't know whether "12345" was 1, 23, 45 or: 123, 45 or: 1,
> 234, 5 etc. - so in principle, you could erroneously match those three
> strings, as just one string.
>
> I realize this might not happen with the actual numbers in question. But[/color]
as[color=blue]
> a matter of principle, it would be safer to do the fixed-length thing.
>
> TC[/color]


Hi TC
This did cross my mind, however if you look at the select query, you will
see that I group first by MyField then by GetAscString([MyField]). This
means that the first 'group by' looks at the string - it doesn't need to
'understand' the code to correctly group 'test', 'Test' and 'TEST' together,
and 'one', two', 'three' apart. In other words, with the word 'test' for
example, 116,101,115,116 is already correctly grouped as the word 'test' -
it couldn't be grouped as 11,61,01,115,11 6.
It is only when (ignoring upper/lower case) the word is the same, that the
second 'group by' is needed and even then, the code generated is not in the
select clause so the user doesn't see it.
Therefore, I can't see a case (even theoretical) where this query would not
correctly group a list of words. However, experience has taught me to
anticipate being corrected.
Regards

Fletcher

[TC]

Re: Case sensetive query


"Fletcher Arnold" <fletch@home.co m> wrote in message
news:bn5btu$4mf $1@sparta.btint ernet.com...[color=blue]
> "TC" <a@b.c.d> wrote in message news:1066802481 .214681@teuthos ...[color=green]
> >
> > "Fletcher Arnold" <fletch@home.co m> wrote in message
> > news:bn1pmk$nb3 $1@sparta.btint ernet.com...
> >
> > (snip)
> >[color=darkred]
> > > As an alternative to Lyle's which does not involve changing data types[/color][/color]
> you[color=green][color=darkred]
> > > could simply write a query. Asuming the data 'Test', 'test', 'TEST'[/color][/color][/color]
etc[color=blue][color=green]
> > is[color=darkred]
> > > held in a field called MyField in the table MyTable, then the query[/color][/color]
> would[color=green][color=darkred]
> > > be:
> > >
> > > SELECT MyField, Count(*) AS MyTotal
> > > FROM MyTable
> > > GROUP BY MyField, GetAscString([MyField])
> > >
> > > You would simply have to paste the following function into a code[/color][/color]
> module:[color=green][color=darkred]
> > >
> > > ' *************** *************** *************** ********
> > > Public Function GetAscString(va rInput) As String
> > >
> > > Dim lngCount As Long
> > > Dim strOut As String
> > >
> > > If Len(varInput) > 0 Then
> > > For lngCount = 1 To Len(varInput)
> > > strOut = strOut & Asc(Mid$(varInp ut, lngCount, 1))
> > > Next lngCount
> > > End If
> > >
> > > GetAscString = strOut
> > >
> > > End Function
> > >
> > > ' *************** *************** *************** ********[/color]
> >
> >
> > You would want to pad the Asc values to a fixed length, eg:
> > format(asc(...) , "000").
> >
> > Otherwise, you don't know whether "12345" was 1, 23, 45 or: 123, 45 or:[/color][/color]
1,[color=blue][color=green]
> > 234, 5 etc. - so in principle, you could erroneously match those three
> > strings, as just one string.
> >
> > I realize this might not happen with the actual numbers in question. But[/color]
> as[color=green]
> > a matter of principle, it would be safer to do the fixed-length thing.
> >
> > TC[/color]
>
>
> Hi TC
> This did cross my mind, however if you look at the select query, you will
> see that I group first by MyField then by GetAscString([MyField]). This
> means that the first 'group by' looks at the string - it doesn't need to
> 'understand' the code to correctly group 'test', 'Test' and 'TEST'[/color]
together,[color=blue]
> and 'one', two', 'three' apart. In other words, with the word 'test' for
> example, 116,101,115,116 is already correctly grouped as the word 'test' -
> it couldn't be grouped as 11,61,01,115,11 6.
> It is only when (ignoring upper/lower case) the word is the same, that the
> second 'group by' is needed and even then, the code generated is not in[/color]
the[color=blue]
> select clause so the user doesn't see it.
> Therefore, I can't see a case (even theoretical) where this query would[/color]
not[color=blue]
> correctly group a list of words. However, experience has taught me to
> anticipate being corrected.
> Regards
>
> Fletcher[/color]


Ah, you are exactly right!

Cheers,
TC