Select largest value from record?

[Kstoney]

Just been using Access for a couple months... new to it (or any database).

I need to select the largest value from one of four fields within a record so that I may use it in another expression.

I have looked and looked through the Help file.

Any ideas?
Thanks.

 

[sailorjwd]

Well... you could probably use nested iif's. But the more fun approach would be to transform your table so that each of the four fields goes into one field but into a new record. You do this with a union query. Something like as follows:

select fieldx, fieldy, field1 as field_new from table1
union
select fieldx, fieldy, field2 from table1
union
select fieldx, fieldy, field3 from table1
union
select fieldx, fieldy, field4 from table1

note that fieldx and fieldy are the fields in your table other than the 4 fields in question.

Next, use this query as input to a new query where you group by fieldx, fieldy and select max(field_new)

The will give you the answer you want (I think).

Joe.

http://www.blueclaw-db.com/

 

[WildHare]

Here's a little function that returns the largest of a number of values passed to it. Stick it in a standard module and go for it.

------------------------------------------
Function aMax(ParamArray arr() As Variant)
' return max value of n..m passed
' example : foo = aMax(1,17,6,34,199,0,2)
' foo = 199
Dim i As Integer, temp As Integer
temp = arr(UBound(arr))
For i = LBound(arr) To UBound(arr)
If arr(i) > temp Then
temp = arr(i)
End If
Next i
aMax = temp
End Function
-------------------------------------------

Sailor Joe gave you what would be a good response too, assuming your data was not normalized in the first place. But it may be, you might just want to compare three or four guys in a record, and it's not necessarily a given that they should be transformed..

Whatever works is best.

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm