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