Most recent date

[David]

what criteria can I use in MS Access in design view to only show the most
recent date when multiple dates are resulted? for example if a query results
in a records with 9/01/2009, 9/02/09, 9/03/09, etc. it should only show the
latest date available.

 

[Ken Snell MVP]

The way to do this depends upon the use to which you will put the query --
use for a report (displaying data), use for a form (editing data), use as a
data source for another query, etc.

One way:

SELECT Field1, Max(DateField) AS DesiredDate
FROM TableName
GROUP BY Field1;

 

[Dale Fye]

David,

Generally, when I see this type of post, it is because the user wants to
display the most recent record for a particular store or individual or
something along those lines, and as Ken mentioned, how you deal with it can
be dependant on what functionality you want.

1. If you are not worried about the record being updateable (report or a
form where you are just displaying information, you could do something like:

SELECT T1.*
FROM yourTable as T1
INNER JOIN (SELECT StoreID,
MAX(SalesDate) as MostRecentSales
FROM yourTable
GROUP BY StoreID) as T2
ON T1.StoreID = T2.StoreID
AND T1.SalesDate = T2.MostRecentSales

If find that this type of query generally runs quicker than the following
query, but this is not updateable.

2. Another way to do this is:

SELECT T1.*
FROM tblSalesData as T1
WHERE T1.SalesDate = DMAX("SalesDate", "tblSalesData", "StoreID = " &
T1.StoreID)

This method is updateable, but will take longer to run agains a large
dataset because it has to evaluate the DMAX( ) domain function for each
record.