Calculation of differences between fields of records

  • Thread starter Thread starter Webmaster
  • Start date Start date
W

Webmaster

Hi everyone,

I want to collect data, for example of electronical counters.
The table looks something like

CounterID Date CounterReading Difference CountPerDay
A123 20020601 50.000 xxxxx
B456 20020610 80.000 xxxxx
C789 20020705 122.123 xxxxx
A123 20030601 100.000 xxxxx
B456 20030610 123.456 xxxxx
C789 20030705 234.123 xxxxx

Now the problem:
When a user types in the next record and updates
the CounterReading field, i.e.
A123 20040120 160.000
the difference to the last record of the same CounterID
(here line 4, date of 20030601)
should be calculated in the difference field ----> 60.000.
In the CountPerDay field should be calculated the consumption
per day (60.000/number of days).

Any help appreciated in advance

Michael
 
Why do you want to store calculated fields?
You should do this on the fly
but you could do Something like...

varsql = "SELECT MAX(CounterReading) AS MAXReading FROM CounterTable WHERE
CounterID = 'A123'"

before the new record is added but after the data has been added to the form
(an unbound form)

open the recordset

then write the data to the table

varsql = "INSERT INTO tbl_Counters (CounterID, Date, CounterReading,
Difference) VALUES (
A123, Date(), 160.00, 160.00- rs!MaxReading)
docmd.runsql varsql

the above is not code but someything to give you an idea

hope this helps
Dave
 
Back
Top