Sorting Problem - Numbers and Letters - Max Value or ? 1

[pdldavis]

Hi, I have a table that lists drawings and revision numbers.

Revision Numbers is a text field. Revisions are lettered A - Z when a drawing has not been issued.

Revisions are numbered starting with 0 (zero) when a drawing has been released for use.

Example:

Dwg Rev
001 A
001 B
001 0
001 1
002 A
002 0
002 1
002 2

I need to obtain the latest revision only, which in this case would be Rev 1 for dwg1 and Rev 2 for dwg2.

Any help would be appreciated. I am a little stumped.

Thanks, Dan

 

[belovedcej]

I recommend you create an autoID field for this table.

Then instead of looking for the kind of information entered, just look for the MAX ID.

 

[pdldavis]

Hi, I would if I could but I cannot change the table. I am on loan to another department and was told not to change the table structure. That's why I am kind of stuck on this one.

Any other suggestions?

Thanks, Dan

 

[cmmrfrds]

Val(Rev)

If Revisions are always numbers this will only return the numbers.

 

[pdldavis]

Yes, what they do is if a drawing has not been issued for general use they letter it.

If it has been issued they number it.

The report has to show the latest Rev of the drawing whether it is a number or a letter.

I dunno... I was hoping to be able to fake it somehow.

 

[Golom]

Faking it

Select Dwg, MIN(IIF(IsNull(A.N), A.L, A.N)) As [Latest Rev]

From 

(
SELECT Dwg, NULL As [L], MAX(Rev) As N
FROM Drawings As X
Where IsNumeric(Rev)
Group By Dwg
union
SELECT Dwg, MAX(Rev) As L, NULL As N
FROM Drawings AS Y
Where NOT IsNumeric(Rev)
Group By Dwg
) As A

Group By Dwg

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Golom]

or with a bit less verbage

SELECT A.Dwg, MIN(NZ(A.N,A.L)) AS [Latest Rev]

FROM

(

SELECT Dwg, NULL As [L], MAX(Rev) As N
FROM Drawings 
Where IsNumeric(Rev)
Group By Dwg

UNION

SELECT Dwg, MAX(Rev) As L, NULL As N
FROM Drawings 
Where NOT IsNumeric(Rev)
Group By Dwg
) AS A

GROUP BY A.Dwg;

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[pdldavis]

That worked, thanks. Appropriate aliasing too, lol.

Thanks Again,

Dan