Help needed with query for weekly totals

[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

 

[Jerry Whittle]

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.

 

[M.]

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]

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

Thanks
Terry

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.

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

 

[Michel Walsh]

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

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

Thanks
Terry

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.

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