Query for most recent date

[Dan Neely]

I'm trying to pull the most recent versions of data from a history
table. Each row contains the item being modified, the date the change
was made, what the change was, and which user made it. For each item
being modified I want the most recent change, if I only have the
itemID and DateChanged columns in the query I can do it by enabling
the Total row in the query builder, and set the itemID to groupby and
dateChanged to Max. I also want to show the changeTypeID and UserID
fields for the change identified by the previous two fields, but none
of the options in the Total dropdown appear to be an ignore option.



The approach in the link below gave me a start, but there are columns
in my data that I want to display while ignoring in the groupby
command.
http://forums.aspfree.com/microsoft-access-help-18/querry-for-the-most-recent-date-18528.html

 

[Jeff Boyce]

Dan

One approach would be to do it in two steps.

The first step is what you've already done, find the Max(DateChanged) per
ItemID.

The next step is to join the results of the first query back to the table,
to find the other data from the rows that have matching ItemID and
DateChanged. Unfortunately, if you have more than one matching DateChanged
for a particular ItemID, you don't know which one will show up from this.

The solution is to use DateTimeChanged (Now(), not Date()). That way,
unless you have REALLY fast users, a LOT of them, and REALLY bad luck, no
two folks will kick in a row at exactly the same time.

(kind of a brute force approach, but I've used it successfully).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dan Neely]

Dan

One approach would be to do it in two steps.

The first step is what you've already done, find the Max(DateChanged) per
ItemID.

The next step is to join the results of the first query back to the table,
to find the other data from the rows that have matching ItemID and
DateChanged. Unfortunately, if you have more than one matching DateChanged
for a particular ItemID, you don't know which one will show up from this.

How do I set up one query to use a second as input?

 

[Jeff Boyce]

Dan

Open a (new) query. Select the (old) query as if it were a table (use the
"+" button, select the query tab). As far as query2 is concerned, query1 IS
a table.

Regards

Jeff Boyce
Microsoft Office/Access MVP