Trying to glean log information

  • Thread starter Thread starter Sean McGilloway
  • Start date Start date
S

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?
 
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top