Results 1 to 4 of 4
  1. #1
    Shannonvip is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Location
    philadelphia
    Posts
    2

    Help with the Max feature

    I have a table that contains shipping records for the month of January. This table contains the serial number of the item sold which is unique, the customer name and the date it was sold. If a customer returned an order or one unit off of that order we put those items back in stock and resell them out. If they get sold out in the same month, January for example, it will show up on the report as many times as it was sold out. So, I can have one serial number show up on this report, that I upload as a table, multiple times with different customer and date information. The only record I need to keep is the one with the latest date. I have been manually deleting the records, which takes forever. Someone mentioned to me that they thought there was a max function that could help me but they didn't know how to do it.

    Can someone be so kind as to walk me through this?

    Thank you in advance.


    Shannon

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I think you can use an Aggregate Query to get what you want, like this:

    1. Create a new query
    2. Add the Serial Number and Date fields to it
    3. Click on the Totals Button (looks like a Sigma). This will add a Totals Row with the phrase "Group By" under each of the two fields.
    4. Under the Date field, change the "Group By" option to "Max"

    Now, if you view this query, it will show you the Max date for each Serial Number.
    To get the value associated with that date, do the following:
    1. Create a new query, joining the query you just created with your original data table on both the Serial Number and Date fields
    2. Return the Serial Number, Date, and Amount fields from your original table

    If I have understood your correctly, I think that should give you what you are looking for.

  3. #3
    Shannonvip is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Location
    philadelphia
    Posts
    2
    Thanks Joe! What if there is other data that I also need but didn't mention because it wasn't important. Other fields like sales rep etc.? I need the entire table to show up with the results. I am understanding and created the query with "group by" and "max" but I am getting lost and part two. I know how to run a query off of a query and a table. Thanks so much

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    In the second query, you can actually add as many fields as you like from your initial table (you could even add all of them, if you like!).

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

Similar Threads

  1. Search Bar Feature
    By Underw7519 in forum Access
    Replies: 1
    Last Post: 08-27-2013, 01:39 PM
  2. Login Feature
    By Yanni in forum Access
    Replies: 1
    Last Post: 01-22-2013, 01:29 AM
  3. Directory Creation Feature Help
    By MWB in forum Access
    Replies: 6
    Last Post: 10-27-2010, 01:50 PM
  4. Comparison Feature Help
    By Kapelluschsa in forum Access
    Replies: 2
    Last Post: 10-25-2010, 06:43 AM
  5. Help with a search feature or combo box
    By jmanis in forum Forms
    Replies: 14
    Last Post: 07-06-2010, 06:38 AM

Tags for this Thread

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