3 Replies Latest reply on Oct 3, 2010 5:06 AM by HermanKloppers

    Calculating Nett values betwen records



      Calculating Nett values betwen records


      Each record consists of a month text field, (10 * labels) for the house nr's and 10* text fields for the electricty usuage to be inserted. Against each apartment/house number the accumulative electricity reading for the specific month is entered -  the following month, the new accumulative readings (identical apartments) is entered via a new record – I require the program to deduct the previous reading from the Current reading to determine the Nett usage for the particular month

      Is this possible ?

        • 1. Re: Calculating Nett values betwen records

          I would suggest to:

          1. create an ID field (number, auto-generated serial)

          2. create a number field called previous_month_ID

          3. use the field to create a self-join relationship between previous_month_ID and ID, which will yield a second table occurence

          4. create a calculation field with a formula that substracts the current reading from the reading in the second table occurence.

          I suggest you use number fields instead of text for the usage reading.

          edit: This solution is quite generic and you have to make sure that one can select the last month's reading for every reading (eg. via a dropdown menu, using a value list based on ID and selecting "Show (only) values from second field", putting more sense into the dropdown menu than just an ID number)

          • 2. Re: Calculating Nett values betwen records

            Instead of data for 10 apartments in one record, you would be better off to have 10 different records each labled by month and house number. The resulting simpler structure will make many data entry and reporting tasks simpler for you. You can still use the method suggested by jonasg, but the relationship would match by both previous month and house number.

            Instead of a serial number for month ID, I'd use this date calculation, originally posted by Comment, which can then be used to display the month and year on each record:

            cMonth:   DateField - Day ( DateField ) + 1   Where DateField can record any date in the month. The calculation takes that date and returns the date for the first day of the same month.

            You can use a calculation field, Date ( Month ( cMonth ) - 1 ; 1 ; Year ( cMonth ) ) to link to records for the previous month.

            • 3. Re: Calculating Nett values betwen records

              Thank you both for the support - Created the fields and calulated the dates as suggested but I must be missing a small detail as every time I link the records, the identical readings are displayed for the Previous and Present months