Filter for Day of the Week (part 2)

Q

Qaspec

I'm using the following to count results by day -

SELECT Weekday([Message Received],2) AS DOW, Count(zComQry.[Thread ID]) AS
[Count]
FROM zComQry
WHERE (((zComQry.[Message Received])>([Forms]![MainForm]![DStart]) And
(zComQry.[Message Received])<([forms]![MainForm]![DEnd]+1)))
GROUP BY Weekday([Message Received],2);

I'd like to be able to also count the number of times the day appeared
during the date range. So if the date range was 7/11 to 7/18 then wednesday
would be 2 and the other days of the week would be 1. Then I'd like to take
that number and come up with an average from the count.

Example:

Date Total
7/9/2007 3
7/10/2007 4
7/11/2007 3
7/12/2007 6
7/13/2007 1
7/14/2007 1
7/15/2007 0
7/16/2007 2
7/17/2007 3

Id like to return a result of

Day: Total: Rep: Avg:
Monday 5 2 2.5
Tuesday 7 2 3.5
Wednesday 3 1 3
Thursday 6 1 6
Friday 1 1 1
Saturday 1 1 1
Sunday 0 1 0

I'll probably also have to account for a 0 Rep if the date range is less
than 7 days.

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top