find a record and move back (1 Viewer)

[rschultz]

Using Access 97 I need to find any time there is data in the ADJ field, then take that data, move back one record, and place it in the ADJ field in that record. Data will only appear in a table once or twice and always in the record after where it should be. Anyone know an easy way to do that?

 

[Pat Hartman]

Relational tables do not have any particular order unless they are specifically sorted. Therefore, in order to make this work, you will need a unique identifier with an ascending value such as an autonumber. You'll need two queries, one to find the specific row that needs updating using:

Select Max(UniqueId) As MaxId
From YourTable
Where UniqueId < [Enter some Value];

Then use the result of the above query to join back to the main table to do the update.

PS, if you don't have a unique sequential identifier (or timestamp), you won't be able to do this at all.

 

[rschultz]

hmmmm, doesn't each record have a number? can't I find the record with the data, put that record number into a variable and then go to record number -1 (or something like that) then place the data I found into that record?

 

[Pat Hartman]

Relational tables do not have record numbers that are associated with specific records as Xbase tables like dBase do. The record number you are seeing is actually a counter and refers to the record's ordinal position in the recordset. Depending on how a particular recordset is ordered, the same record could easily end up with different record numbers in each recordset.

However, that does give me an idea that might help you. You can write a sub that reads through a recordset and when it encounters an adjustment, gets the saved bookmark of the previous record, goes to it and updates it. Be careful not to get yourself into an endless loop with this process. The second time you encounter the adjustment record in your forward march through the recordset, you'll have to bypass it.

You will still need to get this recordset ordered properly to be able to do this.