Cant have aggregate function in expression?

[jahlmer]

Help w/ Access 97 Query

I need the average of a count. (Actually, I don't even know what I need really). I have a timestamp for calls. I need to break them down into a daily count, then average that.

I tried

SELECT avg(Count(*)) AS Expr1
FROM tblNewCalls
WHERE (((tblNewCalls.Date)>=Date()-7));

But of course, no go. I'm new to SQL and VBA but know access pretty good otherwise...

Any suggestions on how to accomplish this will be greatly appreciated. Should I use a subquery for this?

Thanks.

 

[braindead2]

SELECT Count(*)/7 AS avgeragcalls
FROM tblNewCalls
WHERE (((tblNewCalls.Date)>=Date()-7));

 

[jahlmer]

Wow, what does the /7 do? This certainly seems like it works, but I still don't know how. An explaination would help me keep from having to post these dumb questions.

Thanks for the post.

 

[braindead2]

SELECT Count(*)/7 AS avgeragcalls

select count(*) returns the count

/7 divides the count by 7. Since you were returning 7 days worth of records

AS avgeragcalls assigns an alias to the field