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?
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?