Find value from a different record in the same tab

[rmconlon]

Here is my problem. I am creating a system of accounting statments. From each statement a specific amount is held back for 1 year and then liquidated. I am trying to figure out a way to look up the old statements and fill in the liquidation amount 1 year later.

For example:

Statement 1 - Date 1/1/2003 - $100 held for liquidation

Statement 2 - Date 1/1/2004 - Need to have automatic lookup to 1 year previous and plug in $100

Any suggestions?

I would like this to be either automatic, or trigger a script to lookup the value from the previous statement and insert it into the new statement.

[NFD]

First, using your example, Statements 1 & 2 MIGHT use separate tables IF #1(Old) are all Statements, and #2(New) are all Liquidations.

But, assuming 'new' statements have some unique field in common with 'old' statements, say for example, [OriginalID#], a field can be defined to lookup the liquidation amount using a self-join relationship. This may not be necessary, because this relationship would also allow you to display the original liquidation amount in the new record and/or use it in your calculations.

If, on the other hand, you're currently creating 'new' statements that do NOT have any unique field in common with the original, it might be better to use the User Interface to find the original statement first, then build a relationship that allows the creation of related records based on the ID# of the original statement (and the above applies).

The below probably doesn't apply, but just in case;

If you're trying to update alot of existing, but currently un-related records with the original liquidation amounts, you'll need to consider what criteria you might use to determine which 'old' record corresponds to which 'new' one.

You might narrow your choices by creating a temporary calculation field [OriginalDate] = Date(Month(StatementDate), Day(StatementDate), Year(statementDate) - 1), then build a temporary self-join relationship in which StatementDate = OriginalDate. A portal can then show only records showing statement dates of exactly 1 year prior. If you can ascertain enough criteria to end up with only 1 related record for each 'new' record, you can then create a field that looks up the ID# of the original record in all 'new' records and then delete the temporary field and relationship (and the above applies).

You could do the same thing with a loop script that sets the current RecordID to a Global field, performs a Find based on criteria matching the current record and (if the found count = 1), copies the ID# and Liquidation amounts, returns to the first record, pastes these values, then proceeds to the next record to repeat the process. But if this is a one time process, it's probably easier the other way.