Calculations on table data

[sstasiak]

I have a table in my DB, tblWeightUpdate, which stores patient weight history. When a patient comes in for a treatment, their current weight[Weight] is taken and input through my main form along with the date[CurrDate] it was taken.

These entries are linked to patients in my main table, tblOncReg, by a medical record number[MEDRECNO].

I have a report that opens from my main form, which displays that patients weight history, starting with the oldest date. Next to the date fields in the report, I'd like to have the weight fluctuations from visit to visit.

Example:

Weight Date taken Variation

175 11/1/2006
185 11/21/2006 +10
171 1/5/2007 -14

Any ideas on how to do this? Keep in mind I'm an Access newbie and I'm using Access '07.

 

[llkhoutx]

Databases manipulate records not variations between records.

You accomplish what you want by cycling through the table recordset, record by record comparing the current vs the 9savved) previous, eg

dim db as dao.database
dim rs as dao.recordset
dim intPrevious as integer
dim intVariation as integer

set db=currentdb
set rs=db.openrecordset("tblOncReg",dbopensnapshot)

rs.movefirst
intPrevious = rs!Weight
'do whatever with rs!DateTaken
rs.movenext
do
  intVariation = rs!Weight - intPrevious
  intPrevious = rs!Weight
  ' do whatever with rs!DateTaken, intVariation 
  rs.movenext
until rs.eof
rs.close
db.close
set rs=nothing
set db=nothing

Forgive the typoes, if any. Hopefully you get the principle.