>Hi all again,
> I'm doing some computations on a records in a table using the SCAN ... ENDSCAN. I have a variable (var1) which stores the compound number (1, 2, 3,..) and var2 (1, 2, 3,..) which stores the house number. Var2 is incremented by one from the previous record if the compound is the same otherwise var2 starts from one again. I was wondering if there's a function in VFP6 that can lag values (something like the LAG() function in SAS!). Or how do I go about this? I want my code to "see" the previous value of compound (var1) and/or house (var2).
>
>Thanks in advance guys.
>Richard.

Let's say you have another fields called Base, and within Base, you can have multiple occurrences of different compounds.

Base    Compound   House
----    --------   -----
1           1        1
1           1        2
1           1        3
1           1        4
1           2        1
1           2        2
1           2        3

2           1        1
2           1        2
2           1        3
2           2        1
2           2        2
2           2        3
2           2        4

What I would do is create 2 parameterized views, and requery them as needed

create sql view lv_max_compound as select max(compound) Compound ;
   from mytable where base = ?nBase

create sql view lv_max_house as select max(house) House ;
   from mytable where compound = ?nCompound

When the Base and/or Compound changes or after adding a new record [and updating any buffered changes], all you have to do is:
nBase = mytable.base
nCompound = mytable.compound
requery('lv_max_compound')
requery('lv_maxhouse')