Help needed with query for weekly totals

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have a table with orders for the year. What I want to do is create a
query that shows a sum of each product sold by Item Number, but grouped by
weeks of the year. i.e. all sales for product Number 100 for first week of
2007, total for same product number for second week of 2007, etc.

Any help suggestions would be greatly appreciated.

Terry
 
If this is for a report, let the Reports Wizard do it for you. It can help
you create a report that is grouped both by Item Number then by Week. I
believe that the default is Month but you can change that at the drop down.
 
Transforming a date into a week number:

Format(DatePart("ww";[Datefield]);"00")

Format is optional if you prefer week 1 to be displayed as 01

The final query would have to be grouped by product and weeknumber and the
sum of the numbers sold.

Good luck,

M.
 
Sorry, but I don't understand how to put this into a query. Can you
explain?

Thanks
Terry

M. said:
Transforming a date into a week number:

Format(DatePart("ww";[Datefield]);"00")

Format is optional if you prefer week 1 to be displayed as 01

The final query would have to be grouped by product and weeknumber and the
sum of the numbers sold.

Good luck,

M.

Terry said:
I have a table with orders for the year. What I want to do is create a
query that shows a sum of each product sold by Item Number, but grouped
by
weeks of the year. i.e. all sales for product Number 100 for first week
of
2007, total for same product number for second week of 2007, etc.

Any help suggestions would be greatly appreciated.

Terry
Click to expand...
Click to expand...
 
In the query designer, you place the expression in the first line, instead
of a field, kind of a computed expression. You keep the suggested GROUP BY
in the total line, under it.


Vanderghast, Access MVP


Terry said:
Sorry, but I don't understand how to put this into a query. Can you
explain?

Thanks
Terry

M. said:
Transforming a date into a week number:

Format(DatePart("ww";[Datefield]);"00")

Format is optional if you prefer week 1 to be displayed as 01

The final query would have to be grouped by product and weeknumber and
the
sum of the numbers sold.

Good luck,

M.

Terry said:
I have a table with orders for the year. What I want to do is create a
query that shows a sum of each product sold by Item Number, but grouped
by
weeks of the year. i.e. all sales for product Number 100 for first week
of
2007, total for same product number for second week of 2007, etc.

Any help suggestions would be greatly appreciated.

Terry
Click to expand...
Click to expand...
Click to expand...
 
Back
Top