Calculate periodic totals from running total

[lzuke]

Raw beginner here.

At our office we have a door counter that counts customers as they enter AND leave the building. (so it counts them twice)
The door counter ONLY provides a running total. It never resets. (I have no control over this)

Every morning before we open, our department has to record the number from the door counter, which is basically the running grand total customers as of the End of Day yesterday and then we calculate the total number of customers that came in by comparing the door counts from one day to the next. We currently do this easily in Excel, but we are creating a very simple database for various statistics and want to include this statistic in with the others. The basic formula is:

([EOD Door Count] - [Previous EOD Count]) / 2 = Total Customers for the day (we divide by 2 because counter counts customers twice)

In Access, I want to have a DAILY CUSTOMERS table in which we only input the EOD Door Count and then Access will store that number as well as a calculated Daily Total Customers for each day. Each record would include DATE / EOD DOOR COUNT / DAILY TOTAL CUST

I don't know how to tell Access to refer to the PREVIOUS EOD door count in order to calculate the customer total.



Example:

Friday morning the door counter says 564,388 (previous to that it read 564,188)
564,388 - 564,188 = 200 customers/2 = 100 customers came in on Thursday.

Monday morning the door counter says 564,488
564,488 - 564,388 = 100 customers/2 = 50 customers came in Friday.

(Note: We don't do this EVERY day (when we're closed), so it just needs to refer to the previous door count from the last entry in order to make the calculation.)

[pbaldy]

Normally you wouldn't store the total, just calculate it on the fly. One way:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[lzuke]

Thank you, Paul. The meter reader scenario seems to be very similar. I'll try working with that.

I did consider whether I really needed the calculation in the table, but thought I'd be prepared to have it. The reason I decided to calculate it there is because I wanted staff to see right away how many people came in the day before, just so they know, and also as a safeguard in case they typed in the big number incorrectly. "Oops, no way we had 4230 customers yesterday. Better double check that." Right or wrong, that was my thinking anyway.

[pbaldy]

You can save it if you want, but if somebody goes back and edits a reading for last month, every saved value since then needs to be fixed.