Creating Running Totals in Tables

[jcaswell]

Please can someone help me with a particular problem I am having in Access 97 - I have a table that is keyed by 'user name' and date (actually week ending). Against each key combination is a number (in this case number of hours). I want to maintain a running total over a year of the weekly hours. e.g:

Wk 1 2 3 4 5 etc.
Hrs 4 15 3 0 0 etc.
Running 4 19 22 22 22 etc.

The Hrs figures are progressively entered over the year using a form (User Name) and sub-form (all weeks and hours). I want to update the Running Hours whenever the Hrs are amended. I cannot guarantee the order of entry, or that changes may not be made, although the only thing that can be changed will be Hrs.

Can someone help me with this? I suspect that I will need to use VB (which I know little about) - if so can someone send a sample or example?

Thanks in advance - I have been wrestling with this for days.

 

[Pat Hartman]

Storing calculated data is normally poor practice and storing a running total is even worse. You said yourself that you cannot guarantee entry sequence. Think about what you would have to do if entires are made out of sequence.

The proper solution is a report to calculate the running totals as needed.

 

[jcaswell]

Pat

Thanks - however, even being able to calculate in real-time would be useful (and, as you say, better).

I have tried many ways to do this, and thus far I have failed. Any suggestions?

Thanks

 

[jcaswell]

Further to the above, I think that I will need to do some recursion or sub-selects, which I am rusty about (10 years since I did serious programming) and i am unsure how Access would deal with it.

 

[Pat Hartman]

You can calculate running sums by using the DSum() function provided each row has a unique identifier and the recordset is ordered by that unique identifier. The DSum() needs to look for rows with key values <= the current one and sum them. This method is EXTREMELY inefficient and potentially too slow to be useful. As a programmer, you should understand why. Depending on how many records are in the recordset, each row could potentially need to be read thousands of times. Remember, each record, requires that all other records with a lower key value be summed. Whereas a report or batch program that made a temp table would only need to read the recordsource once to accomplish the same thing.

[This message has been edited by Pat Hartman (edited 03-17-2002).]

 

[jcaswell]

I have tried playing with DSum with limited success. In doing so I got more frustrated - the RunningSum facility in reports does exactly what want - however - I want a chart, not a list on a form.

I am quite happy to create a temp table from which to chart - can you possibly give me a sample of the SQL that would do this? The main fields are: ResInits (Text), WeekEnding (Date) and Hours (Number). Unique key will be combination of ResInits and WeekEnding.

The query should result, for every ResInits, in a list of WeekEndings with Hours for each WeekEnding (there will only be one entry for each WeekEnding so there is no need to total them) and a calculated running total of Hours.

For example:
Res Inits W/E Hours RunTot
AA 10/3/02 5 5
AA 17/3/02 2 7
AA 24/3/02 0 7
AA 31/3/02 0 7
----- etc. ------
BB 10/3/02 15 15
BB 17/3/02 10 25
BB 24/3/02 0 25
BB 31/3/02 0 25
---- etc. -------

Thanks

 

[RV]

I think you don't need a temp table and you don't need to use DSum.

From your first post, I understand you've got a table with three columns.
I've called them UserName, WeekEnding and Hours.
Say your table is called UserWeeks, try this SQL statement:

SELECT UserWeek.UserName, UserWeek.WeekEnding, UserWeek.Hours, SUM(UserWeek1.Hours)
FROM UserWeek, UserWeek AS UserWeek1
WHERE UserWeek.WeekEnding>=UserWeek1.WeekEnding
AND UserWeek.UserName=UserWeek1.UserName
GROUP BY UserWeek.UserName, UserWeek.WeekEnding, UserWeek.Hours;

Suc6,

RV

 

[jcaswell]

RV - Thanks for your response. However - it is not giving me what I want - the SQL I typed in is as follows (after some re-formatting by Access):

SELECT Utilisation.[Res Inits], Utilisation.[Week Ending], Utilisation.Hours, Sum(Utilisation1.Hours) AS SumOfHours

FROM Utilisation, Utilisation AS Utilisation1

WHERE (((Utilisation.[Week Ending])>=[Utilisation].[Week Ending]) AND ((Utilisation.[Res Inits])=[Utilisation1].[Res Inits]))

GROUP BY Utilisation.[Res Inits], Utilisation.[Week Ending], Utilisation.Hours;

What this gives me is not a running total for each resource by week but the same total against each week:

Res Week Hours Running
AA.....1......5........20
AA.....2......15.......20
AA.....3......0........20
........etc..............

Rather than:

Res Week Hours Running
AA.....1......5........5
AA.....2......15.......20
AA.....3......0........20
........etc..............

Have I typed the SQL wrong?

Thanks for your help so far

John

 

[RV]

John,

>Have I typed the SQL wrong?<

Yeah, you have.....

(((Utilisation.[Week Ending])>=
[Utilisation].[Week Ending])
should be:

(((Utilisation.[Week Ending])>=
[Utilisation1].[Week Ending])

Adapt and try, it's just what you've been looking for...

Greetings,

RV

 

[jcaswell]

RV

Thank you very much - finger trouble - does just what I want

John