Adding prompt responses as as a field value

[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

 

[Klatuu]

Put two text boxes on your form, one for start date the other for end date.
The fitler the query based on the values of the controls. For example, lets
say your form name is frmWeeklyTotals and the control names are txtWeekStart
and txtWeekEnd. In the query builder you would use the criteria row in the
Sales Date column to do the filtering:

Field: [Sales Date]

Criteria: BETWEEN Forms!frmWeeklyTotals!txtWeekStart AND
Forms!frmWeeklyTotals!txtWeekEnd

 

[KARL DEWEY]

In design view add a field by copying the prompt like this --
Week Ended: [Ending Sales Date]

In SQL view it would look like this ---
SELECT [Product], Sum([Sales]) AS [Sales], [Ending Sales Date] AS [Week
Ended] ...
WHERE [Sales Date] Between [Beginning Sales Date] AND [Ending Sales Date] ...

 

[RT_Indy]

Thank you both for your responses. Karl, the design view solution worked
great and is exactly what I needed.
--
Robert

In design view add a field by copying the prompt like this --
Week Ended: [Ending Sales Date]

In SQL view it would look like this ---
SELECT [Product], Sum([Sales]) AS [Sales], [Ending Sales Date] AS [Week
Ended] ...
WHERE [Sales Date] Between [Beginning Sales Date] AND [Ending Sales Date] ...
--
KARL DEWEY
Build a little - Test a little

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