Hi guys, i'm fairly new at this so bear with me.

I'm creating an ATM cash management relational database on Access 2007. Really I need a mentor for the whole thing but this is the important thing at the moment. I have three tables:

tblATMfigures - just daily totals recorded & checked
tblLOAD - captures how much cash has been loaded into the machine
tblBALANCE - end of week data capture (what is left, calculates balance)


The problem lies with tblLOAD, or qryLOAD which contains:

ID
TP (Trading Period)
BP (Balance Period)
Date
10s (Number of,)
20s (Number of,)
Then from these the query works out:
10s Total
20s Total
£10 Value
£20 Value
Total Value


This, as you can imagine is easy enough so far. But in order for tblATMfigures to do the job I want it to, I need a running Total value of the load plus all other loads for that Balance period (Thurs - Weds). Then at the end of the BP for it to be reset. I figured out how to do an overall running total on the query and a stepped report showing weekly running totals but I couldn't work out how to change the week commencing day. But a report output would be no good as I need to use the weekly running total for tblBALANCE

I did an A-Level in ICT (Relational Databases) three years ago but can barely remember a thing! I'm trying to prove to my dad that a database is so much better than a spreadsheet but i'm not succeeding at the mo!

Any help would be much appreciated. Also feel free to shout at me for leaving some crucial information out! Email me if you'd like to have a looks at the database - Mike_014_@hotmail.com

Many Thanks!

This, as you can imagine is easy enough so far. But in order for tblATMfigures to do the job I want it to, I need a running Total value of the load plus all other loads for that Balance period (Thurs - Weds). Then at the end of the BP for it to be reset. I figured out how to do an overall running total on the query and a stepped report showing weekly running totals but I couldn't work out how to change the week commencing day. But a report output would be no good as I need to use the weekly running total for tblBALANCE

you probably dont - if you need these figures at any time, you should calculate them based on the current date range etc - the thing is to design the database (ie tables/fields) so that this info is EASY to generate. The general ruile is not to store anything that can easily be calculated. And running totals are especially tricky - what if you delete something in the middle, or add another item in the middle.