One approach would be to group a query by week, month, year etc. To get the
sum for each product you need to group by the product also. For the years
and months you can use the year function. For the week you can use the
DatePart function; this takes various interval arguments, that for week being
"ww". When grouping by month or week, however you also need to group by
year, or rows for the same months or weeks from different years will be
grouped together.
So for yearly grouping the query would be something like this:
SELECT YEAR([Date]) AS [Sale Year],
Product, SUM(Qty) AS [Total Quantity]
FROM [HTGL-HISTORY]
GROUP BY YEAR([Date]), Product;
For month
SELECT YEAR([Date]) AS [Sale Year],
MONTH([Date]) AS [Sale Month],
Product, SUM(Qty) AS [Total Quantity]
FROM [HTGL-HISTORY]
GROUP BY YEAR([Date]), MONTH([Date]), Product;
And for weekly grouping:
SELECT YEAR([Date]) AS [Sale Year],
DATEPART("ww", [Date]) AS [Sale Week],
Product, SUM(Qty) AS [Total Quantity]
FROM [HTGL-HISTORY]
GROUP BY YEAR([Date]), DATEPART("ww", [Date]), Product;
If you want to restrict the results to a particular date range you can also
include a WHERE clause with parameters for the start and end of the range,
e.g. in the case of the weekly grouping:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT YEAR([Date]) AS [Sale Year],
DATEPART("ww", [Date]) AS [Sale Week],
Product, SUM(Qty) AS [Total Quantity]
FROM [HTGL-HISTORY]
WHERE [Date] BETWEEN
[Enter start date:] AND [Enter end date:]
GROUP BY YEAR([Date]), DATEPART("ww", [Date]), Product;
When using date/time parameters it’s prudent to declare them as otherwise a
date entered in short date format could be interpreted as an arithmetical
expression and give the wrong result.
An alternative approach would be to create a report to return total
quantities per product for all three intervals simultaneously. Base the
report on the following query:
SELECT YEAR([Date]) AS [Sale Year],
MONTH([Date] AS [Sale Month],
DATEPART("ww", [Date]) AS [Sale Week],
Product, Qty
FROM [HTGL-HISTORY];
You can again include a WHERE clause with parameters if you wish to limit
the result to a specific date range. As you'd be showing all three intervals
in the report, however, it would be best to restrict the result to full years
or the figures could be misleading, e.g.
SELECT YEAR([Date]) AS [Sale Year],
MONTH([Date] AS [Sale Month],
DATEPART("ww", [Date]) AS [Sale Week],
Product, Qty
FROM [HTGL-HISTORY]
WHERE YEAR([Date]) = [Enter year:]
OR [Enter year:] IS NULL;
Its not really necessary to declare the parameter in this case. By testing
for the parameter being NULL it becomes optional, so if no year is entered
all years are returned.
Group the report via the sorting and grouping dialogue in design view by
Product, Sale Year, Sale Month and Sale Week. Give the Product group a group
header and give the last three groups group footers. Set the height of the
report's detail section to zero and in each group footer put an unbound text
box with a ControlSource of:
=Sum([Qty])
Also in each footer, alongside the unbound text box, put a text box
(labelled Year, Month and Week Number as appropriate) with ControlSources of:
[Sale Year]
=Format([Date],"mmmm")
[Sale Week]
The second of these will show the month as its name rather than number.
In the product group header put a bound text box with a ControlSource of:
[Product]
BTW I'd suggest you avoid using date as a column name; it could be confused
with the Date function. If you do use it be sure to include it in square
brackets.
Ken Sheridan
Stafford, England
JCowell said:
([HTGL-HISTORY]![Date]) Between Date()-14 And Date()-8
I'm using the above statement as a criteria in a Query. It is not working
and I'm getting errors.
What I want to do is get a Sum for Product Qty sold between two given dates.
Most products are sold every day and my database hase qty sold for each date
but I want to be able to sum those for each week, month, year, etc.
I have tried a host of various expressions but I think my problem is that I
don't know how to use the "Between" expression with dates. If it can not be
used with dates is there something of the equivalent I could use?
