update a field using the previous record's information

[gcyeung]

Hi, Hope someone can help me with this. I have a table with data in following format:
250,25
251,25
252,25
253,25
254,25
that I want to add 2 new columns like following
250,25,1,4
251.25,5,4
252.25,9,4
253.25,13,4
254.25,17,4
where the value in the 3rd column is the sum of the values of 3rd and 4th columns in the previous record (except for the 1st record which the value 1 is fixed in the 1st line.
Can I do this with macros? with Jet SQL or I can only write function for this dynamic data update?
thanks..

 

[dhookom]

If your table has only the two columns that you show, then where do you find "the sum of the values of 3rd and 4th columns in the previous record"? There are no 3rd and 4th columns.
There is also no such thing as a previous record unless you specify a specific field value that establishes a sort order. You have the first column that seems to be consecutive, incrementing numbers. Is this ALWAYS the case?
Also, it really helps a lot if you share your table and field names. If we make them up then you have to convert back to your names and this some times causes mistakes.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[OU18]

I have a similar situation where i would like to take the record before and increase the field by one. Here is my structure

Unique ID - AutoNumber
BatchHeader - LC - this is constant, never changes
BatchID - 12012003 - This field needs to be increased by one each day
BathIdUsed - YES/NO, this signifies whether the batchID has been utilized or not and tells the DB to use the next batchID.

My question is how can I increase the BatchID field by one once the last one is used.

UniqueID BatchHeader BatchID BatchUsed
1 LC 12012003 Yes
2 LC 12022003 Yes
3 LC 12032003 Yes
4 LC I want to increase batch ID to 12042003 and so on and so on.

Thanks for the help
Steve