looking for greatest between 5 columns?

[syphlix]

Hello there,



example table has 6 columns

Date, A, B, C, D, E

1/1/11, 1, 2, 3, 4, 5
1/2/11, 3, 2, 3, 5, 3

I'd like to make a query that will return the date and the greatest of the 5 columns A-E

i.e. it would return
1/1/11, E
1/2/11, D

Is this even possible? I can't seem to figure out how to do it and i'm wondering if something is wrong with the way my tables are set up.

Thanks!

[pbaldy]

I would say you have a design issue; the fields should probably be records in a related table. If you stay with it, you might be able to modify this to resolve your problem (what about 2 with the max value?).

http://support.microsoft.com/default...b;en-us;209857

[syphlix]

Hrm - any hints on how I can build it differently?

Right now my main Data table is

Date, BaseValue, A, B, C, D, E
1/1/11, 2, 3, 4, 5, 4, 4
1/2/11, 4, 2, 5, 7, 9, 11
Then i made a query that finds the difference between the A-E and the basevalue such that:

Date, A, B, C, D, E
1/1/11, 1, 2, 3, 2, 2
1/2/11, -2, 1,3, 5, 7

How can i make it differently so I can do that comparison I asked about in the OP?

Does this make sense?

[pbaldy]

Normalized design would be something like this (guessing, not knowing what the fields contain):

Main table
Date, BaseValue
1/1/11, 2
1/2/11, 4

Detail table
Date, Field, Vaule
1/1/11, A, 3
1/1/11, B, 4
1/1/11, C, 5
...

With that design the SQL Max() function finds you largest value (or difference from base value). A technique like this finds the other data:

http://www.baldyweb.com/LastValue.htm

[syphlix]

You're right - i knew something was wonky with the way things were set up...

Can i get your help on how to convert the weird table into the way you mentioned?

Right now the way i get the data is from a spreadsheet that is basically

Date, Symbol, Price1, Price2, Price3, Price4, Price5

There isn't too much I can do about the format it comes in, but is there a way to somehow convert that into

Date, Symbol, Price#, Value

?

Date, Symbol would be the key that i would use to relate to another table

[pbaldy]

You can use a UNION query:

SELECT [Date], Symbol, 1, Price1
FROM TableName
UNION ALL
SELECT [Date], Symbol, 2, Price2
FROM TableName
UNION ALL
...

"Date" is not a good field name, as Access can confuse it with the Date() function.