Search for most recent entries

[DRKENNE]

I'm generating a query when I want to returmn the most recent date in a field
where there are multiple dates associated with an event. I'm lookin gfor
somethin simple I can use in design query. The following is a snap shot of
the information contained in the table (sorry it didn't copu over well):

EventID RankID Priority PriorityDate
474 2078 3 - Medium Priority Investigation 25-Sep-07
474 2078 2.0 - Medium Priority Characterization 12-May-08
474 2078 1.1 - High Priority/Emergency response 09-Jun-08
477 2525 1 - High Priority Investigation 28-Sep-07
478 1882 8 - Pending Closure 20-Sep-07
478 1882 5.0 - Pending Closure 12-May-08
478 1882 5.0 - Pending Closure 11-Jun-08
484 502 3 - Medium Priority Investigation 09-Oct-07
484 502 2.0 - Medium Priority Characterization 12-May-08

 

[Ken Snell \(MVP\)]

SQL would look like this:

SELECT * FROM YourTable
WHERE PriorityDate =
(SELECT Max(T.PriorityDate)
FROM YourTable AS T
WHERE T.EventID = YourTable.EventID);

To do this in design view, create a query, add YourTable to the grid, select
the * column and the PriorityDate column and move them to the grid below.
Uncheck the Show box for PriorityDate. Put this expression (change generic
names to real names) in the Where: box under PriorityDate;

(SELECT Max(T.PriorityDate)
FROM YourTable AS T
WHERE T.EventID = YourTable.EventID)

 

[Ken Sheridan]

Using a subquery, as Ken Snell describes, to determine the latest date per
event is the usual approach, but if you want to avoid having to write any SQL
at all you can do it entirely via query design view:

1. Create a 'totals' query grouped by EventID and selecting Max for
PriorityDate.

2. Create another query joining, the original table to the above query on
both EventID and PriorityDate (the latter will probably be called
MaxOfPriorityDate in the 'totals' query). In design view you'd do this by
adding both the table and the query to the new query and creating two join
lines between them, from EventID to EventID, and from PriorityDate to
MaxOfPriorityDate. Add only the columns from the original table to the
design grid for the new query, not those from the 'totals' query.

I'd urge you to dip your toes in the murky waters of SQL, however. Its
really not that scary and provided you use sensible table and column names
which are as close as possible to the real world entities and attributes
which they represent you'll find that it reads (and as a corollary to this
can be written) quite logically.

Ken Sheridan
Stafford, England