Results 1 to 4 of 4
  1. #1
    rockell333 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    33

    Using MAX to show largest purchase and the date

    I need to create a new table to see only the maximum purchase group by customer with the date of that purchase.



    I have the correct grouping by customer, and simply chose MAX on the purchase which is great. I see the customer number with the customers maximum purchase. However, I want a third field, "year" that shows me the date of that purchase. If I choose group by then I will still have every purchase.

    I want

    Customer | Their Largest Purchase | Date of that Purchase

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It's 2 queries:
    Q1 get Max,and clientID
    then Q2:join Q1 and the data table joined on client ID to pull all data.

  3. #3
    rockell333 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    33
    ok.. sounds reasonable.. what would happen if they have two purchases same amount?

  4. #4
    rockell333 is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    33
    NVM... I think what I can do there is do a max date on the new table and create and third query for the last table. Excellent worked thanks..

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

Similar Threads

  1. Replies: 4
    Last Post: 03-15-2016, 12:37 PM
  2. Replies: 3
    Last Post: 08-31-2015, 09:04 AM
  3. Show Date Picker on Load and Verifying Date
    By Markb384 in forum Forms
    Replies: 4
    Last Post: 03-04-2014, 07:44 AM
  4. Replies: 1
    Last Post: 11-13-2012, 05:03 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