Got the day, need week and month.

TrunksSenkawa

Registered User.
Local time
Today, 18:55
Joined
Oct 10, 2002
Messages
13
I have a query that sums the number of instances of a field [Department] for every day. I need to create 2 queries; one creates the sums for a week and one for a month.
The left column is the departments and the top row is the weeks.
So in the week, it would display along the row as:
10/20/02 10/27/02 11/03/02

In the month:
10/02 11/02 12/02
 
Last edited:
Post the SQL statement of the query that sums the number of instances.
 
Last edited:
Sql

TRANSFORM Count([Call Log Table].Department) AS CountOfDepartment
SELECT [Call Log Table].Department
FROM [Call Log Table]
GROUP BY [Call Log Table].Department
ORDER BY [Call Log Table].Department, [Call Log Table].txtDate
PIVOT [Call Log Table].txtDate;
 
Assuming txtDate is a date/time field in table [Call Log Table], try these two queries:

qryWeek:-
SELECT Department,
sum(iif(txtDate between #20/10/2002# and #20/10/2002#+6,1,0)) AS [20/10/02],
sum(iif(txtDate between #10/27/2002# and #10/27/2002#+6,1,0)) AS [10/27/02],
sum(iif(txtDate between #11/03/2002# and #11/03/2002#+6,1,0)) AS [11/03/02]
FROM [Call Log Table]
GROUP BY Department;

qryMonth:-
TRANSFORM Count([Department]) AS [The Value]
SELECT [Department]
FROM [Call Log Table]
GROUP BY [Department]
PIVOT Format([txtDate],"mm/yy");


(Add as many weeks as needed in the first query.)
 
Week good

Week works perfectly, thanks! But, the month does not seem to total. I'm getting sums for the month values that are signifigantly less that the week values.
 
On my machine, both qryWeek and qryMonth work.

Are you trying to compare the result of qryWeek with the months in the result of qryMonth? They are not comparable as the last Week in a month in qryWeek may contain days from the next month. For instance, Week 10/27/02 contains 1st and 2nd November.

If you need to make them comparable, you can build qryMonth in the same manner as qryWeek, i.e. using sum(iif(...)) and specifying the start date and end date of each month, e.g.
sum(iif(txtDate between #10/6/2002# and #11/2/2002#,1,0)) AS [10/02]
 
Last edited:

Users who are viewing this thread

Back
Top Bottom