fields for weekly sales, for the last 4 weeks

[teh_chucksta]

Hi, and thanks for reading this,

I’m trying to build a data set from one table that contains weekly sales
information for multiple weeks. For the field name = ‘Sales-$’, how do I
create, in a query, the following four different result sets (?), where the
user will specify the most recent ‘WE Date’ via a prompt:

User enters ‘5/10/2009’ at the prompt and the query provides the following 4
distinct data results:
1. Sales -$, for WE 5/10/2009
2. Sales -$, for WE 5/03/2009
3. Sales -$, for WE 4/26/2009
4. Sales -$, for WE 4/19/2009

If the answer involves a sub query, please show me how to build one. Make
sense?

Thanks in advance,
Charlie

 

[Marshall Barton]

I’m trying to build a data set from one table that contains weekly sales
information for multiple weeks. For the field name = ‘Sales-$’, how do I
create, in a query, the following four different result sets (?), where the
user will specify the most recent ‘WE Date’ via a prompt:

User enters ‘5/10/2009’ at the prompt and the query provides the following 4
distinct data results:
1. Sales -$, for WE 5/10/2009
2. Sales -$, for WE 5/03/2009
3. Sales -$, for WE 4/26/2009
4. Sales -$, for WE 4/19/2009

If the answer involves a sub query, please show me how to build one. Make
sense?

A query can only return one result set. It can be sorted so
the records are presented the way you want. Try using

SELECT [Sales -$], WE
FROM thetable
WHERE WE Between DateAdd("ww" , -4, [Enter End Date]) And
[Enter End Date]