Calculations in MS Access 2010

Question

I have the following question"

My electricity usage database has the following information

1. 

<tfoot></tfoot>

Electr Readings

ID	Date of Entry	Time	Current Reading
1	30-12-19	10:17	67
2	01-01-20	10:00	53
3	06-01-20	10:00	13
4	07-01-20	10:00	1
5	11-01-20	15:00	47
6	13-01-20	19:20	195
7	19-01-20	13:59	114

<tfoot></tfoot>

Electr_Units Bought

ID	Date_bought	Time	Amount	Units	Reading Before_New units	Reading_After new Units
1	30-12-19	10:00	140.00	70	0	70
2	07-01-20	12:00	200.00	95	0	95
3	13-01-20	10:00	350.00	167	28	195

I want to calculate the units of electricity I use/consumed for a period (between two dates or another period).  How can I create a query or a report to calculate the units of electricity consumed, whereas the readings on the prepaid electricity box are recorded as "Readings before new units"? NB: "current reading" indicates how many units are remaining on the prepaid box. These readings are displayed with two decimals.

Please help.

Thank you.

Answer 1

I'll give you an equivalency which should allow you to figure this out, because it is a very common problem. Your scenario is equivalent to inventory processing. You buy and sell (consume) a product, and now want to know the quantity on hand. Tons have been written about this (e.g. http://allenbrowne.com/AppInventory.html). Good luck!

---

-Tom. Microsoft Access MVP

Answer 2

Dear Tom,

Thank you for your reply.

For your information; I am not a database developer, but I am trying simply to set up a database for water and electricity consumption for our staff members at the work. 

I appreciate your reference to the allenbrowne website. I checked it out. What I got there answers 85% of the info and guidance I requested for.  However, the first most important info for me is; how many units were consumed in a period of time? See my example below:

If it was an excel table one could easily determine the units used by adding a column which would calculate the units used between two different dates (for eg for No.2 will be 14 units, no 3 will be 40 units and no 4 will be 12 units used...and so on). That is not that simple with MS Access, and I want to use Access because the data will be increasing over years, and higher quantity of data is better handled by MS Access than Excel.

ID	Date and Entry	Time	Current Reading
1	30-12-19	10:17	67
2	01-01-20	10:00	53
3	06-01-20	10:00	13
4	07-01-20	10:00	1