Access 2007 SQL Stmt returns the incorrect value ?

L

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
 
J

Jerry Whittle

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

No it's not when sorting text as it starts with the left most character and
works right. OSO-6 is bigger than OSO-1. What comes after doesn't matter.

If the "OSO-" was the ONLY problem with your data, you could do something
like:

Min(Val(Replace([Field3], "OSO-", ""))) AS MinOfField3

However that's going to strip off the OSO- and even the leading 0s in the
other fields.

Plus if there are any other data problems, say something like "BSO-", you
still won't get what you want.

You may need a subquery to find the appropriate records.
 
L

Laynester

Mr. Whittle,

Nice sweet little function and it looks very interesting. I'm going to
'test' out some samples, but you're also correct, I'll have other values
other than 'OSO-'

My longest is a 'MUIR-' there are about 40-50 of these front end-number codes.

Thanks so much for that piece of syntax. Using it and testing it out now. If
there are other programming options available the goal is to get this done
inside one select(with any number of sub selects).

Best Regards,

Laynester

Jerry Whittle said:
No it's not when sorting text as it starts with the left most character and
works right. OSO-6 is bigger than OSO-1. What comes after doesn't matter.

If the "OSO-" was the ONLY problem with your data, you could do something
like:

Min(Val(Replace([Field3], "OSO-", ""))) AS MinOfField3

However that's going to strip off the OSO- and even the leading 0s in the
other fields.

Plus if there are any other data problems, say something like "BSO-", you
still won't get what you want.

You may need a subquery to find the appropriate records.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Laynester said:
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
 
J

Jerry Whittle

If all the other problem stuff ends with a - you may be able to use the InStr
function to find the - and then use the Mid function to extract what is after
it. The the Val function will turn it into a number. However that will strip
out leading zeros.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Laynester said:
Mr. Whittle,

Nice sweet little function and it looks very interesting. I'm going to
'test' out some samples, but you're also correct, I'll have other values
other than 'OSO-'

My longest is a 'MUIR-' there are about 40-50 of these front end-number codes.

Thanks so much for that piece of syntax. Using it and testing it out now. If
there are other programming options available the goal is to get this done
inside one select(with any number of sub selects).

Best Regards,

Laynester

Jerry Whittle said:
'OSO-1203758' it's GREATER THAN 'OSO-679558'

No it's not when sorting text as it starts with the left most character and
works right. OSO-6 is bigger than OSO-1. What comes after doesn't matter.

If the "OSO-" was the ONLY problem with your data, you could do something
like:

Min(Val(Replace([Field3], "OSO-", ""))) AS MinOfField3

However that's going to strip off the OSO- and even the leading 0s in the
other fields.

Plus if there are any other data problems, say something like "BSO-", you
still won't get what you want.

You may need a subquery to find the appropriate records.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Laynester said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top