AVG(COUNT(timestamp))...this will not work, what am I doing wrong?

[Mitchell_Collen via AccessMonster.com]

Please help me. I want to find the average of the number of timestamps that
are in several tables. When i run the following, it will not work because I
can only use avg with a number datatype. (at least, that's what i understand)
I am just learning the syntax rules. the following works as long as i don't
include avg. Please help me.

Select avg( count(timestamp),area), datepart(hh,timestatmp), datepart(wd,
timestamp)
From lotsoftables
where timestamp between 05/01/2007 and 06/20/2007
Group by timestamp,datepart(hh,timestatmp), datepart(wd,timestamp),area

Thanks in advance.
-Misty

 

[Guest]

I want to find the average of the number of timestamps that are in several
tables.
Your query is only pulling records from one table - named "lotsoftables."
I assume the the closing parenthsis after 'area' is a typo.
You are counting timestamp records. How do you expect to average a count?
I do not understand the 'hh' and 'wd' you are using. An "h" would be for
hour, "d" for day of month, and "w" for day of the week.

When you count or average items you are rolling them together but you are
also trying to display the timestamp as individual records.

Please explain what you are trying to do a little more. Post sample data,
table and field definitions, and an example of what you expect the output to
look like.