Full Version: Get Latest Date for each record
UtterAccess Forums > Microsoft® Access > Access Date + Time
pracslipkerm
I have a table with an ItemID, Date and StockQty.
I am trying to retrieve only the latest date (and StockQty) for each ItemID.
I have managed to get all itemID's that have the latest date but the ones not modified on the same date don't appear (I used Dmax for this).
Any help would be fantastic, as I am at a impass as to what to try next.
Thanks heaps in advance for your time
Steve
HiTechCoach
Welcome to UA!

One option is to make a totaling query that groups by ItemID and calculating the max date and the StockQTY (which I assume would be a sum).

Then you can add this query to your original query and do a join by ItemID.
SerranoG
Are ItemID, Date, and StockQty the actual names of your fields? If so, change "Date" to something else. The word "Date" is an Access function that gives you today's date. Queries and functions have the potential to malfunction because you're confusing them by accidentally calling the function when you don't mean to.
Aquadevel
I have to agree with Serrano,
re-name' Date to like> DDate or something.
HAs to the latest date entry, a query based on the date in decending order 'should' work'.
Aqua
R. Hicks
They need to do as Boyd instructed and use a Totals query ...
DH
pracslipkerm
Thanks Heaps everyone - I have renamed the field and will try using a totals query. Your help is much appreciated
Steve :-)
pracslipkerm
Thanks to you guys I am now getting the Query to give me the latest date for each item. I am trying to take the associated StockQty and update another table. I tried creating an update qry and updating the Tables Qty to Qty from the query but i get the "Operation must use an updateable Qry".
Ohaven't linked more than 2 items per qry so why doesn't this work???
Any help would be great!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.