none
Calculations in MS Access 2010 RRS feed

  • 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.


    • Edited by Brabee Sunday, January 19, 2020 5:18 PM
    Sunday, January 19, 2020 5:03 PM

All replies

  • 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

    Monday, January 20, 2020 2:09 PM
  • 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

    Wednesday, January 22, 2020 10:08 AM