Repeat the value from the previous record in a query

[Roger]

I have some large Access databases that only a title for a number of records
that have been input. If the title is the same then the field is null. I
would like to add thiese titles to the records but not overwrite ant titles
input.

I would like to write a query that goes through the whole database and adds
the previous value if that field is null.

Any ideas please.

 

[Allen Browne]

You can do this with a subquery if you have a clear way to identify the
"previous" value.

This example assumes you have a primary key field named ID that defines the
order of the records, and you want to duplicate the previous value in a
field named TheValue:

SELECT Table1.*,
(SELECT TOP 1 TheValue
FROM Table1 AS Dupe
WHERE Dupe.ID < Table1.ID
AND Dupe.TheValue Is Not Null
ORDER BY Dupe.ID DESC) AS TheValue2Use
FROM Table1
WHERE Table1.TheValue Is Null
ORDER BY Table1.ID;

Once you check that this yields the right value, change the query to an
Update query, and move the subquery into the Update row instead of the
Field. row.

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html#AnotherRecord

(BTW, it will take a while for this to execute if you have many records.)