bring value forward to field from previous record

[sooner1]

I have a table for generating reports about check runs. Three fields of interest are [First Check Number], [Last Check Number] and [Next Check Number]. There are check runs from several different groups in the same table. Each record has [Group ID] and [Check Run Date] as it's primary keys. What I need to happen is that when I start to enter a new check run for each group, that the [Next Check Number] from the previous check run is set as the [First Check Number] for the new record. Is there a low-overhead way to do this? Any help appreciated.

 

[essaysmith]

I had a similar problem, what I did was to add an Autonumber field to the table, I then ran a query that had a calculated field that subtracted one number from the autonumber and ran another query that linked the table to itself by linking the Autonumber to the calculated field and putting the field from the table with the calculted field one less than the current record (does this make any sense?). I was using a recordset that didn't need to be changed however, because I am not sure if you can edit the records after all of these queries. Perhaps this could be a start.
HTH,
Shawn

 

[sooner1]

Unfortunately, the records would not necessarily be consecutive, since there are more than one group involved. There will be a variable number of records between "last weeks" record and "this weeks". My feeling is that what I need is some way to assign a default value to the [First Check Number] that is based on a query of the table using the [Group ID] and [Check Run Date] fields, something to the effect of "find the [Next Check Number] Value from the most recent previous record where {Group ID] is equal to the [Group ID] in the current record" The [Check Run Date] field would be the first value less than the current date. If I understand your suggestion, it would only work if the records for each week were consecutive (thus allowing the autonumber field to work). Thanks for the suggestion, and if I'm misunderstanding, please let me know.