Recalculate/Update next records based on new value

[Debbie37]

(Acess 97) I am having trouble recalculating and updating census records based on changing values. Census is determined by Date, Unit, Admits, Discharges, etc. Everyday you have a BeginCensus and EndCensus total (adding and subtracting the admits and discharges) for each day, by unit. The EndCensus for today will be the BeginCensus for tomorrow, again, by unit. The original data is imported in (table is overwritten everyday), all fields calculated and appended to a summary table ordered by census date and unit. However, days later we may get admits and discharges that were missed and now need to be added to the summary table for that date/unit. I am having trouble recalculating/updating the BeginCensus and EndCensus totals for each record that follows that CensusDate. For example,

CensusDate Unit BeginCensus Admits Dschgs EndCensus
1/1/08 4100 30 5[6] 3 32[33]
1/2/08 4100 32[33] 3 4[6] 31[30]
1/3/08 4100 31[30] 3 1 33[32]

On 1/5/08 you get 1 additional Admit for CensusDate 1/1/08 and 2 Dschgs for 1/2/08. Values need to be recalculated/updated for each following date to current in the table for that unit (recalc values in braces). I can update values for that date, but am having difficulty updating the following records…

 

[PHV]

Have a look here:

http://r937.com/relational.html

You've discovered yourself one main reason to not store derived/calculated vlaues ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Debbie37]

What exactly are you referring to? I am not unfamiliar with basic dbase structure, but am wanting some options in performing update calculations on specific record sets.

 

[ProgramError]

What PHV is saying is dont store the running totals but the admits and discharges of the particular day. so youre table would look like.
CensusDate Unit Admits Dschgs
1/1/08 4100 1 0
1/2/08 4100 0 1
1/3/08 4100 0 1

BeginCensus would be a calculated field
The number number of admits less the number of dschgs between CensusStartDate and CensusEndDate

EndCensus would be a calculated field using
EndCensus = BeginCensus + Admits - Dschgs

the table then can easily be updated ie

CensusDate Unit Admits Dschgs
1/1/08 4100 [blue]2[/blue] 0
1/2/08 4100 0 [blue]2[/blue]
1/3/08 4100 0 1

One other thing I would think about. Will you be using 4100 unit throughout the table or will it change? Will this be used in any queries? Do you need to store it or could you use it in any calculations you may need it in ie result = 4100 + 6


Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.