update field based on previous value

[deejay_totoro]

Hello,

Any help would be appreciated!

I would like to populate field 1, based on the last value (not null) found in fld1. For example in recno 2 and 3 fld1 should be "1"… and recno 9 - 11 should be "4".

Here are some examples (below.)

Best wishes,

Dj_totoro


Original

recno Fd1 fd2

01 1 red
02 _ blue
03 _ yellow
04 2 grey
05 _ green
06 3 black
07 _ orange
08 4 purple
09 _ pink
10 _ cyan
11 _ white

Desired result

recno Fd1 fd2

01 1 red
02 1 blue
03 1 yellow
04 2 grey
05 2 green
06 3 black
07 3 orange
08 4 purple
09 4 pink
10 4 cyan
11 4 white

 

[namliam]

You want to fix this in your table? Or in a query?

Does your table have a (autonumber) key? or something that can be sorted upon?

 

[deejay_totoro]

Hello,

Well the record number is added during the import stage. The data is coming from an Excel spreadsheet.

It is not possible to specify when to change to another value as the data will change each time.

cheers!

 

[dkinley]

Not sure if this would help, but this thread ...

http://www.access-programmers.co.uk/forums/showthread.php?t=155670

... has a demo database that could be used for your purpose with a little modification.

The global variable stores the data of the previous record. Instead of comparing the previous number to the current - all you would need to do is have it check for a null value and apply the variable to the field (if it is null). The big thing is that the records would need to be sorted properly so when this is applied it is applied directly. You could copy your table into the demo, make the modification and test it.

Again, just an option - hope that helps.

-dK