Total query by week or by month

[Jim W]

I have a very simple form in which users enter data daily. I am trying to write a query that will give me the totals weekly. I want my weeks setup Monday to Sunday. My problem is that I can not figure out how to set the Date to 7 days then total. Does this make any sence.

Jim

 

[Jon K]

You can use a Totals Query to group by Week Numbers using DatePart("ww",[DateField],2).

Alternatively, you can create a lookup table to hold the StartDate and EndDate of each week. In the Totals Query, link the tables with Where DateField Between StartDate And EndDate, and group by StartDate and EndDate.

The second method is more informative. See database attached.

 

[Jim W]

Jon, thank you very much. I like the 1st query. Works very well. For the line DatePart("ww",[DateField],2) can I put criteria on this like a date range so that the weekly data will only be shown for a range that the user selects. I know that this is possible if I use the 2nd example you sent me but I would perfer not to use a lookup table for weeks.
Also is there a way to do a month to month total?

Thanks again
Jim

 

[Jon K]

You can type the week numbers that you want in the Cretieria cell. These are vailid expressions:
16
in (14,16,17)
between 15 and 18

The expression you type will be transformed to the Having Clause of the query:
SELECT DatePart("ww",[Date],2) AS WeekNumber, Sum(tblData.Qty) AS SumOfQty
FROM tblData
GROUP BY DatePart("ww",[Date],2)
Having DatePart("ww",[Date],2) in (14,16,17)


To filter a range of records, you can use Between ... And ... in the Where Clause e.g the following will not include the March 29-31 records in WeekNumber 14:-
SELECT DatePart("ww",[Date],2) AS WeekNumber, Sum(tblData.Qty) AS SumOfQty
FROM tblData
where [Date] between #4/1/2004# and #4/18/2004#
GROUP BY DatePart("ww",[Date],2)

Having and Where can be used at the same time in a Totals Query. Having filters the groups. Where filters the records.


For monthly totals, you can use:-
SELECT Format([Date],"mmm yyyy") AS [Month], Sum(tblData.Qty) AS SumOfQty
FROM tblData
GROUP BY Format([Date],"mmm yyyy"), Format([Date],"yyyy mm")
ORDER BY Format([Date],"yyyy mm")