Max of 12 column on the 13th one 1

[haerion]

Hi all,

I'm trying to get a query that would give me the max number of the 12 column on the same row. These column are the 12 month and the number are the sold item for each month. Each row representing a different product.

I want it like this:
Item Jan Feb Mar Apr May Highest
98767 1 3 10 25 4 25

How can I get the Highest column to show me the highest number of the 12 first column?

Thanks

 

[PHV]

In a standard code module create the following function:

'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

And now the query:
SELECT Item, Jan, ..., Dec
, myMax(Jan,Feb,Mar,...,Nov,Dec) AS Highest
FROM yourTable


BUT, your table is NOT normalized. Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[haerion]

It's work GREAT, thanks PHV!!!

And about the normalization, I don't really want to normalize it more than that i already have like 10 tables all linked together to normalize as much as possible and this table is from a report that is exported into an excel file that i import into access.

Thanks again!