Access 2007 - SQL query assistance needed

[Laynester]

Hello Access 2007 Google Groupmeisters, need your help and support on
this one....

My Table:

Field1 Field2 Field3 Field4
MPIID MPIID 07499750
MEDREC 06 000014180210
MEDREC OHMC 679558
MEDREC OHSC 0385633
MEDREC OSO OSO-679558
MPIID MPIID 08931110
MEDREC OSO OSO-1203758

The Select Stmt:

SELECT Table1.Field2, Min(Table1.Field3) AS MinOfField3
FROM Table1
WHERE Table1.Field2 <> 'MPIID'
GROUP BY Table1.Field2;

The result of the stmt above:

Field2 MinOfField3
06 000014180210
OHMC 679558
OHSC 0385633
OSO OSO-1203758

The issue:

If you look at the last value returned:

'OSO-1203758' it's GREATER THAN 'OSO-679558'

I need the 'OSO-679558' value returned NOT the OSO-1203758' (higher)
value.

How can I do that? I need the min's on the other fields (as in my
select) yet this 'OSO-' makes the HIGHER value number being returned?

Thanks in advance - this has been a real sql 'twister' (for me
anyways) in getting the (lowest) OSO- value needed.

Laynester

 

[John W. Vinson]

If you look at the last value returned:

'OSO-1203758' it's GREATER THAN 'OSO-679558'

I need the 'OSO-679558' value returned NOT the OSO-1203758' (higher)
value.

How can I do that? I need the min's on the other fields (as in my
select) yet this 'OSO-' makes the HIGHER value number being returned?

A Text field is sorted alphabetically, left to right: and the text string
"OSO-6" is in fact greater than the text string "OSO-1". As soon as Access
finds a difference, it's done, basically!

The problem is that *you* see this field as a Number with a prefix; Access
doesn't - it sees it as just a text string, it could be "OSO-XYZ" or "OSO-ABC"
just as well.

This is made more complicated by the fact that there is apparently no
uniformity: some of the values of Field4 have the OSO- prefix, and some don't;
for all I know there may be many other prefixes. In addition, it looks like
some of these numbers are pretty large - 000014180210 is ok, but 100014180210
would be larger than a Long Integer can store, so it might be tricky to sort
it!

ASSUMING (and it's a big assumption, one that you'll need to verify) that
these field values all consist of either numeric digits, or some prefix
followed by a hyphen followed by numeric digits; you could construct a sort
key which will sort in numeric order:

SortKey: Right("00000000000000000000" & IIf(Field4 LIKE "*-*", Mid([Field4],
InStr([Field4], "-") + 1), [Field4]), 20)

This will extract the portion of the field after the hyphen, or the whole
field if there is no hyphen; tack 20 zeros on the front; and trim the result
to 20 characters, giving

00000000000007499750
00000000000014180210
00000000000000679558
00000000000000385633
00000000000000679558
00000000000008931110
00000000000001203758

for your data. You can then find the Min of this calculated value.

If your numbers will never exceed 2 billion odd (ten digits), you can use
CLng(Mid([field4], InStr([fielf4], "-")+1)) instead, it may sort faster.

 

[Lynn Trapp]

Actually, the text value "OSO-679558" is greater than the text value
"OSO-1203758". This is because Access doesn't evaluate the numeric portion of
the field for sorting, but sorts it entirely as a text value. If you have a
table with the following TEXT values in them:
1
2
45
111
21

They will sort as
1
111
2
21
45