Can't get date range in a query

[D.P. Roberts]

Query1 contains the following columns:

Username
TotalLogonHours (uses SUM of data from Query2's column, which calculates
total hours of each logon session by subtracting the logon time from the
logoff time)

I'd like to have a third column in Query1 that displays each user's average
logon hours per day. I tried adding a third column as follows:

AvgHrsPerDay: [TotalLogonHours]/(Max([logontime])-Min([logontime])) but it
gives an error saying I cannot have aggregate function in GROUP BY clause.


Any ideas?

 

[Guest]

Try this
Table Sessions: user, date, logOn, logOff

Query LogOnHours:
SELECT Sessions.user, Sessions.date,
Sum(DateDiff("h",[Sessions]![logON],[Sessions]![logOff])) AS TotalLogonHours
FROM Sessions
GROUP BY Sessions.user, Sessions.date;


Query AvgDailyLogon:

SELECT LogOnHours.user, Avg(LogOnHours.TotalLogonHours) AS
AvgOfTotalLogonHours
FROM LogOnHours
GROUP BY LogOnHours.user;