Complicated Select/Sum Query

[tedhekman]

Hi there! I am pretty new to Access, have been loving learning it! I
have a problem here I can't even begin to figure out. Here is what I
need:
Given 1 Date and 1 Store, retrieve the following information:

1. # of each product sold in STORE for DATE, and for the 3 days prior
to DATE
2. Sum # of each product sold in STORE for days 5-8 previous to DATE
3. Sum # of each product sold in STORE for days 9-12 previous to DATE
4. Sum # of each product sold in STORE for days 13-16 previous to DATE
5. Sum # of each product sold in STORE for days 17-32 previous to DATE
5. Sum # of each product sold in STORE for days 33-48 previous to DATE

So obviously this will be a bunch of queries grouped together. I can't
even get my head around where to start though, as I don't know how to
total X number of records at a time. Can anyone help point me in the
right direction please?
Much appreciated,
-Ted

[Johnny Meredith]

Can you post a summary of the table structure?

[Salad]

te*******@gmail.com wrote:

Hi there! I am pretty new to Access, have been loving learning it! I
have a problem here I can't even begin to figure out. Here is what I
need:
Given 1 Date and 1 Store, retrieve the following information:

1. # of each product sold in STORE for DATE, and for the 3 days prior
to DATE
2. Sum # of each product sold in STORE for days 5-8 previous to DATE
3. Sum # of each product sold in STORE for days 9-12 previous to DATE
4. Sum # of each product sold in STORE for days 13-16 previous to DATE
5. Sum # of each product sold in STORE for days 17-32 previous to DATE
5. Sum # of each product sold in STORE for days 33-48 previous to DATE

So obviously this will be a bunch of queries grouped together. I can't
even get my head around where to start though, as I don't know how to
total X number of records at a time. Can anyone help point me in the
right direction please?
Much appreciated,
-Ted

I might create a text box with the label along the lines of "How Many
Days Prior?" and the op fills in the number of days prior. 0 would be
the current date, 1 = yesterday, etc.

In order to assist the op, create a start/end date range. Drop 2 text
boxes; StartDate and EndDate onto the form. Open the property sheet and
set, under Data tab, Enabled to No, Locked to Yes so the ops can't
change the dates. Set the EndDate to default value to today. As in
=Date()

Then when the person enteres a DaysPrior value, have some code in the
AfterUpdate event along the lines of
Me.StartDate = DateAdd("d",Me.DaysPrior * -1,date())
This will now present to the op the data range you are getting data for.

I'll call this form SaleRpt and use it in the following. Change the
Between line to reflect your form name.

In your query, under the SaleDate field, enter something
Between Forms!SaleRpt!StartDate and Forms!SaleRpt!EndDate

This should start you off OK. I have a funny feeling that once folks
see the flexibility, they might want to enter their own date ranges.