Results 1 to 4 of 4
  1. #1
    keat63 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    5

    Struggling with a date format, need some help.


    Folks

    Sorry for what may appear to be straight forward, but I'm struggling to find the answer.

    I've a table of items purchased. which includes the dates of each and every item in the format dd/mm/yyyy.
    I'm trying to run a query whereby it shows only the last date of the purchase, and it's asscociated cost at that time.
    I've only 3 columns 'PARTCODE' 'RATE' 'DELDATE' with 'DELDATE being the date format column.
    Could someone help me sort the query to show only the last date, I guess it's Group By, but group by what.

    Access 2003 by the way.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It sounds like you want to create a query to return the last "DELATE" for each "PARTCODE".
    So, first create an Aggregate Query that groups by "PARTCODE" and returns the MAX "DELDATE". Do NOT put any other fields in this query (or else it won't work as desired).
    Then, create a second query where you link the first query back to your original table, joining on BOTH the "PARTCODE" and "DELDATE" fields, and return all the fields from the original Table.
    This will return the record that is the latest for each PARTCODE, with all other fields (i.e. "RATE").

  3. #3
    keat63 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    5
    Perfect.
    I've not quite completed my query, but I can already see the results are getting near to where I want to be.
    I wasn't aware that it was possible to link a query inside a query.

    Thanks

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome.
    Technically, you could do this all in a single query, if you wanted to write the SQL code directly instead of using the Query Builder (using an nested query, or another query in the WHERE condition).
    But there is nothing wrong with using two queries to do it. Once built, you only ever need to open the second one to get your results.

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

Similar Threads

  1. Replies: 2
    Last Post: 01-14-2018, 08:59 PM
  2. Replies: 3
    Last Post: 12-30-2016, 10:27 AM
  3. Replies: 3
    Last Post: 08-29-2014, 05:25 PM
  4. Replies: 3
    Last Post: 08-20-2014, 01:47 PM
  5. Replies: 4
    Last Post: 03-12-2013, 06:49 PM

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