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