Previous Record Query

[mlr0911]

Hello All,

I am trying to write a query that will not leave any null values in a field. Basically, I need the query to look at the previous field and put that value in the null field until there is a change in field then add a sequential number at the end.

Example:
ID Field
21111
(Null)
(Null)
(Null)
21112
(Null)
(Null)
(Null)

Desired Results:
ID Field
21111
211111
211112
211113
21112
211121
211122
211123

This will eliminate all null fields and add a sequential number to the end.


Thanks for your help.

 

[Peter Reid]

How is the query sorted?

What are the field names?

 

[mlr0911]

The field name is "ID Field", and sorted by "ID Field"

 

[Peter Reid]

If that is the case, you will never get the example you've quoted (will you?)

 

[mlr0911]

Once the example above has been completed, it would then be sorted by the id field.

 

[Peter Reid]

Sorry, I meant to say;

How is the query that generates your example data sorted?



Also, you wouldn't need to sort by the 'calculated' id field as it would already be in that sequence.

If you want to upload the database with the query and the example data, I'll see what can be done