Calculate running sums

  • Thread starter Thread starter judith
  • Start date Start date
J

judith

I wondered if anyone had any ideas please

I have a query that contains
date
resouces available
resurces required

I have calculated for each date the resouces additional to requirement which
is negative if overrun. What I would really like to show is a running summary
of resorces as the over runs catch up.

Date Hours available Hours required Hours not used
Outstanding
12th Jun 300 400 -100
100
13th June 300 400 -100
200
14th June 300 250 50
150
15th June 300 100 200
0

I am looking for a formula that somehow calculates outstanding as
current hours available - outstanding to date.
I have been trying to use a Dsum on a query where the date < than current
date but I suspect it is getting a bit circular. Maybe I am just thinking too
much in terms of the way I would set it up in Excel.

Thanks
 
Could you do this in a report, rather than just in a query?

Create the report, and add a text box with these properties:
Control Source =Nz([Hours available],0) - Nz([Hours required],0)
Running Sum Over Group
Format General Number
 
For a query based solution, I did this.

I created a tabel (Table1) and entered your example data.

Then I created two queries.

I named the first query "Stage1". The SQL is :

SELECT Table1.Date, Table1.Hours_available, Table1.Hours_required,
[Hours_available]-[Hours_required] AS Hours_Not_used,
[Hours_required]-[Hours_available] AS HNU
FROM Table1
ORDER BY Table1.Date;

(HNU = Hours_Not_Used.)



The second query I named "RunningSum". The SQL is:

SELECT stage1.Date, stage1.Hours_available, stage1.Hours_required,
stage1.Hours_Not_used, CInt(DSum("HNU","stage1","[Date]<=#" & [Date] & "#"))
AS Outstanding
FROM stage1
ORDER BY stage1.Date;



BTW, I get -50 instead of 0 (zero)

NOTE: "DATE" is a reserved word (and the name of a function) and shouldn't
beused as object names.

HTH
 
I am struggling a bit with this. I think the problem is that it is an
iterative process.

I have days when processing can not be met by capacity. This can be carried
over and slotted in on days when I have under capacity. But calculating the
hours to be slotted in each day requires knowing how many hours have already
been slotted in on previous days. In Excel I would just use a Sum( ... up to
the previous period) in the current period calculation but in Access I am
trying to use the same field I am calculating and sum it up to the last
period. I tried your suggestions but when I extended it to my full data set
it didnt seem to work. I am becoming confused

Steve Sanford said:
For a query based solution, I did this.

I created a tabel (Table1) and entered your example data.

Then I created two queries.

I named the first query "Stage1". The SQL is :

SELECT Table1.Date, Table1.Hours_available, Table1.Hours_required,
[Hours_available]-[Hours_required] AS Hours_Not_used,
[Hours_required]-[Hours_available] AS HNU
FROM Table1
ORDER BY Table1.Date;

(HNU = Hours_Not_Used.)



The second query I named "RunningSum". The SQL is:

SELECT stage1.Date, stage1.Hours_available, stage1.Hours_required,
stage1.Hours_Not_used, CInt(DSum("HNU","stage1","[Date]<=#" & [Date] & "#"))
AS Outstanding
FROM stage1
ORDER BY stage1.Date;



BTW, I get -50 instead of 0 (zero)

NOTE: "DATE" is a reserved word (and the name of a function) and shouldn't
beused as object names.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


judith said:
I wondered if anyone had any ideas please

I have a query that contains
date
resouces available
resurces required

I have calculated for each date the resouces additional to requirement which
is negative if overrun. What I would really like to show is a running summary
of resorces as the over runs catch up.

Date Hours available Hours required Hours not used
Outstanding
12th Jun 300 400 -100
100
13th June 300 400 -100
200
14th June 300 250 50
150
15th June 300 100 200
0

I am looking for a formula that somehow calculates outstanding as
current hours available - outstanding to date.
I have been trying to use a Dsum on a query where the date < than current
date but I suspect it is getting a bit circular. Maybe I am just thinking too
much in terms of the way I would set it up in Excel.

Thanks
Click to expand...
Click to expand...
 
I am struggling a bit with this. I think the problem is that it is an
iterative process.
Click to expand...

OK....would you explain what you mean by "iterative process"??

been slotted in on previous days. In Excel I would just use a Sum( ... up to
the previous period) in the current period calculation but in Access I am
Click to expand...

I would too. But Access is not Excel; Access requires a different way of
thinking, almost to the point of forgetting anything you know about Excel.

trying to use the same field I am calculating and sum it up to the last
period. I tried your suggestions but when I extended it to my full data set
Click to expand...

Only three fields are necessary to product results like your example:
[Date], [Hours_available] & [Hours_required]. Since "Hours_Not_used" and
"Outstanding" can be calculated at any time, they shouldn't be stored in a
table.

Would you provide more info on what you mean about "full data set "?

it didnt seem to work. I am becoming confused
Click to expand...

What do you mean by "didn't work"? Was there an error message? The results
were not the values you expected?



HTH
 
Sorry I have not been very clear

My table only contains the fields
LabourDate
hoursAvailable
hoursRequired

I have created your queries as suggested and with the data (1st 9 items) (UK
date format)
labourDate: 1/6/09;2/6/09;3/6/09;4/6/09;5/6/09;8/6/09;9/6/09;10/6/09;11/6/09
hours_available: 4800; 4800; 4800; 4800; 4800; 4800; 4800; 4800; 4800
hours_required:2491.84; 57.27; 0; 0; 312.4; 1713.2; 11601.477; 0; 1292.263

In the outstanding column in the runningSum query I get #Error for the items
up to 5/6/09 then -13289 for the next 4 items.

What I was looking for in the outstanding column is 0; 0; 0; 0; 0; 0;
6801.477; 2001.477; 0

I think where the iteration comes in is that at each stage I need to
calculate the hours_available minus hours_required. If this is positive I can
then reduce the outstanding hours value or if it is negative I need to add
this to the outstanding hours At each stage however the outstanding value is
calculated in connection with the value created in the previous line of the
current query and not from the stage1 query.

I have fudged this by creating a form and stringing along 10 weeks worth of
information across the page. The underlying query calculates 10 new fields,
week1, week2, week3 … etc Then calculated fields in the form under these
values just calculate in an Excel type fashion by looking at the values in
the previous field. Not very neat!

Thanks for your time helping me on this


Steve Sanford said:
I am struggling a bit with this. I think the problem is that it is an
iterative process.
Click to expand...

OK....would you explain what you mean by "iterative process"??

been slotted in on previous days. In Excel I would just use a Sum( ... up to
the previous period) in the current period calculation but in Access I am
Click to expand...

I would too. But Access is not Excel; Access requires a different way of
thinking, almost to the point of forgetting anything you know about Excel.

trying to use the same field I am calculating and sum it up to the last
period. I tried your suggestions but when I extended it to my full data set
Click to expand...

Only three fields are necessary to product results like your example:
[Date], [Hours_available] & [Hours_required]. Since "Hours_Not_used" and
"Outstanding" can be calculated at any time, they shouldn't be stored in a
table.

Would you provide more info on what you mean about "full data set "?

it didnt seem to work. I am becoming confused
Click to expand...

What do you mean by "didn't work"? Was there an error message? The results
were not the values you expected?



HTH
Click to expand...
 
Back
Top