Update blank/null fileds with value of previous row using update q

S

Stephen.D

Hi,

I need to fill the blank fields with the value of the same field of the
previous record.

For example : 3 fields in the tableA, 4 records.

FIELD1, FIELD2, FIELD3

G5AA,EVA,ABC
<blank>,EVB,<blank>
G5BB,<blank>,<blank>
G5CC,EVC,DEF

Expected result :

G5AA,EVA,ABC
G5AA,EVB,ABC
G5BB,EVB,ABC
G5CC,EVC,DEF

How should I do this? Many thanks.
 
A

Allen Browne

The table as it stands does not have any order.
Consequently the concept of "previous record" is undefined.
To solve this, you need to add an AutoNumber to the table.
The autonumber defines the order of the records, so you can proceed.
For this example, we will assume the AutoNumber is named ID.

(If this is actually a linked table, you may need to import it so you have a
table to add the AutoNumber to.)

Having done that, you can execute 3 append queries to fill the nulls with
the value from the most recent non-null value. The append query will use a
subquery to get the value from the previous row. It will look something like
this:
UPDATE Table1 SET Field1 =
(SELECT TOP 1 Field1
FROM Table1 AS Dupe
WHERE Dupe.Field1 Is Not Null
AND Dupe.ID < Table1.ID
ORDER BY Dupe.ID DESC)
WHERE Table1.Field1 Is Null;

You can then do the same kind of thing with Field2 and Field3.

If subqueries are a new concept, see:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top