Select DISTINCT question 1

[barryjohnson]

I have a Acces97 database with several thousand entries. I have a field that's called ProductFamily. When we started using the database several years ago, we only entered UPPERCASE ProductFamily names, such as "BREAKERONE", "BREAKERTWO", etc. Now we need to start using both uppercase and lowercase ProductFamily names. Examples would be ab1234, bb321, AB1234, BB321.

Now my problem... When I do a Select DISTINCT ProductFamily from Table I only get back the AB1234 & BB321. It's like Access thinks the ab1234 & bb321 are the same as the uppercase entries.

Any ideas? I need for Access to "see" them as different distinct values.

 

[blom0344]

Hello Barry,

Try using DISTINCTROW instead of DISTINCT in your SQL statement. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[LittleSmudge]

As far as Access is concerned AB423 is the same as ab423.

Try making a simple demo table with a text field in it and make that field the primary key.
Add first record AB678, then try to add ab678 and you'll get a duplicate primary key violation error.


As for Distinct(ROW) :
DISTINCT checks for distinctness amongest the fields that you choose to output from the query
DISTINCTROW checks all fields including those that are not selected for output

Eg

Field1    Field2    
1           ABC      
2           ABC      
3           abc
4           abc

"SELECT DISTINCT Field2 FROM TableName"
will output
"ABC"
in one record

"SELECT DISTINCTROW Field2 FROM TableName"
will output
"ABC"
"ABC"
"abc"
"abc"
as 4 records because the Field1 makes each ROW unique

So DistinctRow is not going to help you in this.


G LS

 

[blom0344]

Yeah,

This proves one should never answer a thread before consuming a first cup of coffee first. LittleSmudge, you are completely right. Silly, me.
However , we still have to help Barry out with his problem, shouldn't we?
If Access thinks ABCDEF = abcdef, we'll force it to look a little deeper. How about generating a check-field 'on-the-fly' that does distinguish between lower and uppercase?
If i assume that the produktfamily codes start with 2 characters I go for the second character from the string:

Asc(Mid([Productfamily],1,1))

In this case for 'B' this is 66, for 'b' this is 98

So a SQL could be something like this:


SELECT DISTINCT Productfamily.Productfamily, Asc(Mid([Productfamily],2,1)) AS [Check]
FROM Productfamily;

which should return the correct output, cause it creates unique combinations based on the added ASCI code. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[barryjohnson]

Our new ProductFamily 'values' can take on the form:

xx#### or xx#####
XX#### or XX#####
xx####x or xx#####x
XX####x or XX#####x

x & X = alpha
# = numeric

I understand your responses and I appreciate them! You answered my question regarding how Access 'views' lowercase/uppercase, thanks.

I see your thoughts and I will play with it. Maybe I can make something work??? If you already figured it out, please send it along!

Thanks for your help, Barry

 

[LittleSmudge]

Okay, Barry, I'll come back this time with my ergonimics hat on.

If Microsoft has such trouble telling the difference between Upper and lower case text - how reliable do you think you humans are going to be?

Well away from the database diffeculties you are experiencing I suspect your organisation is setting itself up for some major product handling/ product identification issues.


If you must go for the mixed upper and lower case produc codes then the idea from blom above is as good as anything I came up with overnight.
If ( Now or in the future ) you allow MIXED case codes ( eg Ab1234X as distinct from ab1234x then you'll need a Check1, Check2 and CheckLast fields but that is just repeating the type of thing blom has put 3 times.


The next problem you'll come up against is - what are you going to do with this list once it is obtained. You can't cycle through it and match records anywhere because you'll come up with the same problem in the next stage.

AB1234X is the same as ab1234x in the WHERE clause as well as with Distinct.
( Think through your coding strategy again - or have I said that before ? )



G LS

 

[blom0344]

Hello Barry / LittleSmudge,

LittleSmudge is right about the coding strategy and I can't help you out with that. But if your coding strategy (every entry starts with 2 characters) does NOT change, I think there is something that can be done, based on the ANSI value of a ..........z, A ............Z.
The first ranges from 97 to 122, the second from 65 to 90.
If it is acceptable to change your 1-field key to a 2-field primary key, you could add a field to your productfamily table that states whether a code is in upper or lowercase.

Let's say lowercase = 'L' and uppercase = 'U'.
Added field is : 'Codecase'


IIf(Asc(Mid([Productfamily],1,1))>90,'L','U') AS Codecase


So , if you revise your table with such a field, in the where clause you can select "ab1234" by:

*** where productfamily = 'ab1234' AND Codecase = 'L' ***


I'm not stating that this will resolve your problems, because a better coding strategy is the real way to go , like LittleSmudge has pointed out.

**********************************************************
(Actually, a whereclause like
productfamily = 'ab1234' and Asc(Mid([Productfamily],1,1)) > 90

is all that it takes, if you don't want the extra key-field
**********************************************************
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com