Results 1 to 4 of 4
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

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


    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?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,187
    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

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,017
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-15-2014, 06:15 AM
  2. Replies: 4
    Last Post: 11-17-2012, 03:07 PM
  3. Replies: 1
    Last Post: 11-13-2012, 05:03 PM
  4. Replies: 8
    Last Post: 07-13-2012, 04:53 PM
  5. Query to find the second largest year
    By hawkins in forum Queries
    Replies: 2
    Last Post: 07-05-2011, 11:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums