How can I write a query to pull the latest log entry for each obje

[Guest]

Good afternoon. I consider my self a pretty capable query-er, but this
has me stumped.
I have a table of documents, and table of log entries. I would like a
summary query that would list each document once, along with the single most
recent activity. So, just "SELECT Doc, MAX(Date) FROM DocLog GROUP BY Doc",
and connect that query to the docs table, and so forth, right?
Unfortunately, several log entries may have the same date, so I can't assume
that the latest date corresponds to a specific log entry.
Any suggestions? Thank you.
Win XP, Access 2K3

 

[PC Datasheet]

"Date" is an Access reserved and should not be used as the name of a field!!

Since your "Date" field is used to log entries, there's a good chance that
that field also has a time component. Check the code that makes the log
entry to see if Date or Now is used. Now has a time component.

 

[Tom Ellison]

Dear Jon:

If your data is not recorded in such a way that you can determine
which [Date] is the most recent, because there may be several with
that same date for any given Doc, then discriminating between these
several rows for the same Doc with the same Date is not possible. In
order to get such functionality out of the database, you must first
design the capacity to do this into the database. Perhaps what you
would want to do would be to record the time of day as well as the
date to make this possible. In addition, if you do not want there to
ever be two rows for the same Doc with the same Date/Time logged, then
you should make this a unique index to the table (if not the primary
key). By preventing duplication of this compound key, you will have
ensured there can only be one "most recent date" for each Doc. If you
do not implement this, the same thing can happen even when including
the time of day for each log entry, though it would probably be less
frequent.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts