Rolling Total

[freshmaker]

I would like to create a query that includes "Date", "HoursUsed", and a calculated field named "RemainingBalance". The “RemainingBalance” field needs to be computed on a rolling basis and would look something like this:

480 - (Sum(HoursUsed) WHERE Date > (Date - 365 days))

In other words, 480 minus the total number of hours used during the previous 365 days.

Thanks in advance for any help!

 

[ott]

Assuming these fields come from the same table (for this example I'll call the table "datHours") the following line in the Field row of one of the query columns should work:

RemainingBalance: 480-(DSum("HoursUsed","datHours","Date > Date()-365"))

 

[ott]

Sorry,

Take the "()" that following the last "Date" in the line. If you keep them in you will get the remaining hours for the period from today's date back 365 days, instead of 365 days back from the date in the "Date" field of the datHours table.

 

[freshmaker]

Thanks Ott-

That seems works to some degree. Unfortunately when I run the query the "HoursRemaining" value is the same for every date. How can I get the results to show the hours remaining on each and every unique date?

 

[Pat Hartman]

If your Date field is really named Date, change its name. There are many places where Access will confuse your Date field with its own Date function.

 

[freshmaker]

Thanks Pat! Pardon my poor naming. It was a test table I created and it must have slipped my mind. Unfortunately changing it to DateUsed yielded the same results.

 

[ott]

This should work better freshmaker. Sorry, logic problem in the first suggestion:

RemainingHours: 480-(Nz(DSum("HoursUsed","datHours","DateUsed > #" & [DateUsed]-365 & "# And DateUsed < #" & [DateUsed] & "#")))

 

[freshmaker]

Thanks Ott! That is exactly what I was looking for.