R
RT_Indy
I have a table that stores very simple sales data by product by day as below
Sales Date Product Sales
05/01/08 Widgets 5
05/02/08 Widgets 7
05/03/08 Widgets 10
05/04/08 Widgets 10
On a weekly basis, we need to update a "weekly total" table with weekly
totals that are used for reporting. I want to use an append query that
prompts the user for the [Beginning Sales Date] and [Ending Sales Date] to
create range of daily data they would like to group as a week (our fiscal
weeks are not always 7 days long). Additionally, I want to add a “Week
Ending†date that summarizes weekly sales. In the above example let's
assume that 05/01/08 through 05/03/08 is a week. I would like the output to
be:
Product Sales Week Ended
Widgets 22 05/03/08
I don’t mind using either a prompt to allow the user to specify the “Week
Ending†date, or I can use the Date equal to the [Ending Sales Date] prompt.
Problem is that I don’t know how to do either one without having the user
update the query every time they run it.
Any help would be appreciated.
Robert
Sales Date Product Sales
05/01/08 Widgets 5
05/02/08 Widgets 7
05/03/08 Widgets 10
05/04/08 Widgets 10
On a weekly basis, we need to update a "weekly total" table with weekly
totals that are used for reporting. I want to use an append query that
prompts the user for the [Beginning Sales Date] and [Ending Sales Date] to
create range of daily data they would like to group as a week (our fiscal
weeks are not always 7 days long). Additionally, I want to add a “Week
Ending†date that summarizes weekly sales. In the above example let's
assume that 05/01/08 through 05/03/08 is a week. I would like the output to
be:
Product Sales Week Ended
Widgets 22 05/03/08
I don’t mind using either a prompt to allow the user to specify the “Week
Ending†date, or I can use the Date equal to the [Ending Sales Date] prompt.
Problem is that I don’t know how to do either one without having the user
update the query every time they run it.
Any help would be appreciated.
Robert