Accessing other records

[torquilp]

Hi there,

Downloaded the beta and I quite like it so far.

What I would like to be able to do is access the value of the previous/next record in an expression. For example you might have 3 rows of data, and it would be good to be able to access values from record 1 when in record 3.

Example, given the data below:

POLICY_NUMBER NAME DATE
123465 JOHN 01/01/2007
123456 DAVE 01/02/2007
123465 PETER 01/03/2007

It would be good to be able to create an additional calculated field that let you access another record, so you could do something like this (assuming data ordered by DATE in this case):

POLICY_NUMBER NAME DATE PREVIOUS NAME (Calculated Field)
123465 JOHN 01/01/2007 null
123456 DAVE 01/02/2007 JOHN
123465 PETER 01/03/2007 DAVE

Any ideas? Might be able to do it in scripting language but I'm not familiar enough with it yet.

[Aaron]

The basic solution is to create a copy of the table, number the rows in the original table starting with an initial value of 1, number the rows in the copied table starting with an initial value of 2, then set up a relationship between the table and the copied table on these numbers. Because these sequences of numbers are offset by a single value, the relationship is set up between a given row and the next row, allowing you to access the values in the next row. Here's the specific steps you need to follow to do this:

1. Sort the table in the order in which you wish to sequence the data using "Sort" in the "Data" menu.
2. After you've sorted the table, open the "Copy Records" panel from the "Data" menu and create a copy of the sorted file by simply pressing the OK button in "Copy Records" panel. This step is necessary to "freeze" the table in the sorted order so that the numbered rows reflect the sequence of the sorted data.
3. Once you've created the copies of the tables, save the copies of the table as TableA and TableB using "Save As" in the "File" menu.
4. In TableA, right-click on a column label and select "Create Calculated Field" from the menu.
5. Then, in the calculated field formula builder, type in the formula "RECNO()" and press "OK".
6. In TableB, right-click on a column label and select "Create Calculated Field" from the menu.
7. Then, in the calculated field formula builder, type in the formula "RECNO() + 1" and press "OK".
8. At this point, you should have TableA and TableB open with a calculated field in each table called "Field1" with a sequence of numbers that start at 1 in TableA and 2 in TableB.
9. Set up a relationship between TableA and TableB by selecting "Relationships" from the "Data" menu and dragging TableA and TableB from the project tree into the relationship diagram.
10. In the relationship diagram, set up a relationship between Field1 in TableA and Field1 in TableB by dragging Field1 from the TableA field list onto Field1 in the TableB field list. At this point, you should see a line between Field1 in TableA and Field1 in TableB.
11. Click the "Update Relationships" button.
12. In TableA, right-click on a column label and select "Insert Column(s)" from the menu, which will open a list of the fields in TableA along with the related fields from TableB.
13. In the list of fields, you will see the original fields in TableA with a blue diamond next to them and the related fields from TableB with a relationship symbol next to them.
14. To insert the related fields from TableB into TableA, double-click on the fields or else drag them from the field list into TableA. Then, right-click on the column labels for each of these fields and select "Convert to Fixed Field(s)" from the menu.
15. The fields that you just inserted into TableA contain the values from the corresponding field in the next row of TableA.

[torquilp]

Aaron,

Thanks for that. Seems so simple. I suppose the only issue might be when dealing with very big data sets, but this gives me a workable solution just now,

Thanks

Torquil