Generating results from two different start dates

Hi,

I'm working on my first Access database and have it mostly finished. However, there is a step I cannot figure out how to do.

For simplicity here is what I have:

TABLE: PaintUse
Fields: [StartDate] and [Amount]

QUERY: [StartDate], [Amount], and AdjustedAmount: [Amount]*0.25
Under the criteria for [StartDate] I have >=[Report Start Date] AND <=[Report End Date]

REPORT (using the above Query):
Detail Section: Lists each activity starting with the manually entered StartDate and ends with the manually entered EndDate.
Calculates [AdjustedAmount] for each record

Weekly Footer Section: Calculates the weekly total of [AdjustedAmount]

Report Footer Section: Currently have AnnualAmount = Sum(AdjustedAmount)

What I need is the AnnualAmount to be the last 365 days no matter what the difference between the StartDate and EndDate is. Right now if the difference is 31 days this is all my AnnualAmount encompasses. To get a 365 day span, I have to run the query with a StartDate and EndDate exactly 365 days apart.


So what I am wondering is how, through the Query and/or Report Designer (would prefer not to program in VBA but just put an expression or expressions in the Query builder) can I get the Detail Section to continue to do what it is currently doing (listing all the records between the two manually entered dates) and get the report footer to provide the sum using the EndDate and the previous 365 days.

Thanks.

Steve

Sounds like you need another query.
One for the main data dates,
And the footer for the year.

I would use a form, with text boxes for start and end date. So you don't have to type in the dates over and over.
The form can also have the year dates based on the entered start /end dates.
The main query uses the txtStart and txtEnd.
The footer query (sub report) can use the txtYrStart, and txtYrEnd.

Thanks. I'll give it a try.