Running Sum Adjustment Alternative (1 Viewer)

[madcats]

I work for a homebuilder and earlier this year I wrote an access database to keep track of when we needed to have trusses tested. Each type of home we build have a different style and/or a different number of trusses. Each type of truss has to be recertified after a certain time frame or when a certain number are used/produced.

It was set up for each week the user to enter the type and number of homes that were produced for those dates. Another form was set up to enter the truss tests (how many trusses until the next test and what date the next test was to be done if the quantity had not been met). I have a table of trusses produced by date and a table of tests done by date. I was combining them into a date sorted report using running sum

Everything worked fine until this morning when they tell me that when a truss is close to having to be tested, say within 48 trusses, and the truss test is done and is good for another 500. They need the running total at the test date to now be 500 instead of 548(the way it has been).

I have tried using running sum to make an adjustment in the report with no luck, works great for the first test but combines all the adjustments for my next test.

I don't want to change how the database is set but I am afraid that is what I am up against, any suggestions how I could handle this in the report?

 

[madcats]

Bump

Updated - I am probably not very good at explaining my problem, this maybe simplifies a little bit. In a report, is there a way to keep a value in a detail field (adj) until a record is found (test) that meets the criteria to reset the field. example:

date___rectype_____truss_____qty_____running total_____adj_______adj count
9/1.......begbal............x...........248.............248...............0..................248
9/2.......mfg................x..........-100............148...............0...................148
9/3.......mfg................x..........-100..............48...............0....................48
9/4.......test...............x...........500..............548.............-48.................500***on test change adj
9/5.......mfg................x..........-300.............248.............-48.................200
9/6.......mfg................x..........-190...............58.............-48...................10
9/7.......test................x...........500..............558.............-58.................500***on test change adj
9/8.......mfg................x..........-100..............458.............-58.................400
9/9.......mfg................x..........-100..............358.............-58.................300