Can I use the Max function to find the largest value across multiple columns?

[klnlsu]

I created a crosstab query to accumulate sales per week of our inventory items for multiple weeks with the item numbers being the rows and the sales per week being the columns. I now want to get the value of the largest sales week for each item. Can I use the Max function to do this? If so, what is the proper syntax?

[ranman256]

if you are trying to MAX from many columns, then it sounds like your data is set up wrong and not normalized.
normally youd max 1 col, and col2 would have the item.

tho for your problem , you will want a UNION query. It will be several queries together in a single query. Each single query will pull MAX from 1 col.
qnMaxAllFields=
select [item], Max(col1) from table
union
select [item], Max(col2) from table
union
select [item], Max(col3) from table

[June7]

Alternative is a VBA custom function. Review
http://stackoverflow.com/questions/2...ultiple-fields
https://support.microsoft.com/en-us/...?wa=wsignin1.0

[klnlsu]

ranman256, you are right. I converted my crosstab query to an aggregate query to get my weekly sales. I then used the results in a second aggregate query to get the max value of each item. Thanks for guiding me in the right direction.