Calculations dependent on previous record

Hi,
I have a large application where I need to generate a table in which the values of several fields in record 2 depend on record 1, values in record 3 depend on record 2, etc. Of course this is very easy to do in Excel, but for the size and complexity of my application, Excel really will not work very well for me.

To do this in Access, the only way I know how to do it is to write code to step through the records in the table and calculate the fields. Is there a better way?

Thanks for any ideas.

The recordset method you described is probably the fastest unless you have a way to join the offset records in a query.

RG is spot-on.

You have to understand that SQL is misleading because of presentation issues. Like in The Matrix ("there is no spoon"), in a query there is no previous record.

SQL is based on set theory, for which there IS NO ORDER of records. All records are just set members. The theory behind SQL is that anything you do normally should be considered as though you are doing it to ALL RECORDS AT THE SAME TIME! The fact that a datasheet view of a SELECT query shows record A before record B is an artifact of presentation - not of actual record order.

In fact, the records returned by a query are in the same order as they appear in a table UNLESS you have included a GROUP BY or ORDER BY clause. And the order of record appearance in a table is usually the order in which they were created UNLESS there is a functional primary key on that table.

However, both of these statements leave you with the same problem. There is no SYNTAX for the concepts of NEXT and PREVIOUS because it is the CONCEPT of order that doesn't exist in SQL.

VBA recordsets, though based on SQL as recordsources, create an extra context that encapsulates the SQL context. That is why VBA can do what you want and SQL itself cannot. It is the "extra" context in which VBA can define variables holding what you wanted to remember until another record comes along.

bearing the doc mans comments you ought to think about your data

is it not possible for each row of the table to carry the information that would otherwise be derived from the previous record - you will only have to set this information up once

Having now rained on your parade, here are some thoughts that MIGHT help.

1. When you want to see "previous record" data, there MUST be a way for Access to find what you consider to be the "previous record." Therefore, if you have not allowed for this situation, it is a design flaw. (Based on you not realizing the implications of SET theory, which is eminently forgivable for new Access users, so don't take it too hard.) This is based on the "Old Programmer's Rule" that says "Access can't tell you anything you didn't tell it first." Which means - in practical terms - that if order means something to you, you must give Access the data required to remember and later impose that order. If you have no variable to identify proper order with respect to your data set, you cannot impose the desired order later.

Very simple issue, black and white. Want order? Need ordering variable! No ordering variable? No order!

2. You can SOMETIMES do something like a DLookup in a query where you look for the record that would precede the current one based on some order identifier.

e.g. if you were ordering by date/time fields and meant "previous" to imply the record with the next earlier time than the record in focus, you would choose the record with the maximum date less than the date in focus. Look at the DMax function. Also notice I said "record in focus" not "current record." This is a fine point, but "Current" also implies ordering by connotation. ("Previous" and "Next" imply order by denotation, a stronger definition.)

Anyway, contemplate this little jewel:

DLookup( "[myvalue]", "mytable", "[mytable]![mydate] = #" & CStr( DMax( "[mydate]", "mytable", "[mytable]![mydate] < #" & CStr( [mydate] ) & "# )" ) & "#" )

I don't guarantee that the parentheses are balanced for the functions and I don't guarantee that the syntax is exactly right. Use Access Help on DLookup, DMax, Cstr, and on strings (in functions) in order to get the exact syntax. The idea is to use a query (implied by DMax) to find the largest date less than the date in focus in order to feed a query (implied by DLookup) to find the value for the record having that date. And the CStr converts the date/time variable to a string so you can use the "#" signs as date-string brackets.

IF you are dealing with different dates for records with different qualifiers, you will also have to include the rest of the qualifies in BOTH the DMax and DLookup functions. That syntax gets awfully nasty awfully fast. Which is why folks take up VBA in the first place.

3. Neither DLookup nor DMax will impose locks since they can't change anything... but YOU might impose a lock that could block the DLookup and DMax if YOU are going to modify something. So... the DLookup/DMax approach works best in SELECT queries. Any UPDATE, INSERT, or DELETE query has a chance to self-block if it contains the DLookup/DMax construct. The self-block is not that DMax or DLookup blocks the action query, but that the action query blocks the DMax or DLookup. Which is why when you try this for an action query, the query MUST be set for optimistic locking if at all possible. And if that is a new term for you, look it up in Access Help under query properties.

4. When you get frustrated enough with the messy syntax of trying to "fake" SQL into knowing which record is "previous" - come back and ask the VBA questions you are sure to have. Perhaps I'm being pessimistic, but for a person not really familiar with Access, the VBA path will be simpler and cleaner in the LONG RUN. No telling about short run issues though.

Thanks,
I started this assuming that I would have to write VBA code (which I know how to do, no problem there); I just wanted to make sure I wasn't missing anything.

Well, in that case I am pleased to tell you that your take on the matter was correct. VBA is in your future.

May I ask what code you finaly came up with. This is very similar to an issue I am attempting to resolve. Thanks so much?

Raising an old thread from the dead is not a good idea. Start a new thread and reference this one if you think it is appropriate.

Sorry about that, I was hoping to get an answer back form the people who worked on this specific issue. I will start a new thread.

You limited your replys to only those that participated in this thread by doing what you did. There are lots of helpers on this site that can assist in your issue. Thanks for starting a new thread.