Trying to glean log information

[Sean McGilloway]

I have a log book that holds lots of log entries about the activities
of my users. Activity #25 is used to note a user's activity (like 'out
to lunch' or 'on business trip for 5 days, send calls to voicemail').
It has the following pertinent fields:

LogID (autonumber key field)
Date (date+time of the log entry)
User (User name string)
Activity (activity number. in this query i just want activity 25)
Details (various details that range from "couldn't find file" to "out
to lunch")

I'm trying to get a status list of users for the firm that looks like:

Lisa / 11-19-04 / Out to lunch
Mark / 11-15-04 / On vacation, back in December
Jim / 11-19-04 / In
John / 11-19-04 / Out sick - call assistant for details

But when I run this query:

SELECT log_Scripts.User, Max(Format([log_Scripts]![Date],"m/d")) AS
Today, Max(log_Scripts.LogID) AS MaxOfLogID,
First(log_Scripts.Details) AS FirstOfDetails
FROM log_Scripts INNER JOIN lookup_Activity ON log_Scripts.ActivityID
= lookup_Activity.ActivityID
GROUP BY log_Scripts.User, log_Scripts.ActivityID
HAVING (((log_Scripts.ActivityID)=25))
ORDER BY Max(log_Scripts.LogID);

The problem is that when I run the query above, the query doesn't
match up the MAX(LogID) with the FIRST(Details). My results will show
Lisa's latest log entry ID number (great), but the [Details] field
data isn't the [Details] data for that log entry number.

Example, the log table lists:

ID# / USER / DETAILS
0 / LISA / Out to Lunch
1 / LISA / Sick
2 / LISA / Playing at the beach

And the query shows:

2 / Lisa / Sick


I *thought* that sorting the data by log entry number, and selecting
the FIRST or LAST of the [Details] field would yield the right info,
but it doesn't. Does anyone know why?

 

[[MVP] S.Clark]

First, you must get the latest log entry date per person, then match that
result to the actual log to get the other field values.

I do this with two queries, but it can be done with one using a subquery.

My method would use a query like:

qryMaxLogDatePerPerson:
Select PersonID, Max(LogDate) from Log Group By PersonID

Use that query to link to the log using both the ID and the date found.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-