Running Total Based on a Date in a field

[scratchmb]

I have a database that tracks consecutive miles a driver has driven without an accident. I would like for the running total to reset to 0 whenever the driver has an accident but don't want to lose historic data in case it needs to be referenced.



Anyone have any ideas.

[aytee111]

Calculated fields should never reside on a table. Your "consecutive miles" is a calculated field, therefore will not exist but will be calculated every time is is needed (forms, reports, etc.). The values that will be kept on the table will be those that are required to do the calculation, i.e. date of last accident, miles driven by date. Your calculation will be done using those two fields stored on tables.

You could also have a table that stored accident data which would contain the historical information, although this is not required. It could store the number of miles from last accident to this accident, which would never change and would never need to be recalculated.

[scratchmb]

Ok, you'll have to excuse me, I know enough to get myself in trouble. I understand what your saying, but can't get the calculation to work since [dateoflastaccident]

I have a table with miles per week [miles] [payperiod] and i have a table with accidents by date [dateofaccident] what would the expression look like for the calculated field.

thanks for your last post and like I said i know just enough to get in trouble.

[aytee111]

Firstly, don't apologize - we all started somewhere.

It is a difficult calculation because you don't carry the information that is required. Here are the steps you need to do - get it straight in your mind before trying to get the computer to do it:

1) Analyze the calculation and write it down in words, e.g. number of accident-free miles = number of miles covered since last accident
2) figure out where each bit of the equation is going to come from
a) number of miles covered
b) date of last accident
The last one is easy as it is a field stored on a table
The number of miles covered is the hard one as you rather mixing apples and oranges here. Say they have an accident on Tuesday the 15th. You know they covered 500 miles in the pay period (11th thru 17th). Let's say they had a to-date total of 1,000 miles. Now you, the designer, need to work out how to calculate it - is the answer going to be 1,000 miles (as of the end of the previous pay period) or is it going to be 1,500 (the end of the following pay period) or some other calculation such as 500 miles divided by 7 days multiplied by 4 days (til Tuesday).

Once you have written the calculation out and know exactly what you want, then - and only then - can it be translated into Access.

[aytee111]

Forgot to mention - date of last accident could be blank, you will need to have another date for those cases. Date of hire?