Update Query - Update field with previous field value

[Bill Clark]

I have about 20,000 records pulled from Excel that I need to update.
What I need to do is run an update query that bascially says: If a
field is null, update it with the previous record value of that same
field. In some instances, it will have to go back a few records
before it finds a value that is not null. Can this be done?

Thanks

Bill

[Allen Browne]

This example assumes:
- the table is named "MyTable";
- the field to be updated is named "MyField";
- the primary key field is named named "ID".

1. Create a query into this table.

2. Drag MyField into the grid.

3. In the Criteria row beneath this field, enter:
Is Null

4. In the Field row, enter this calculated field:
( SELECT TOP 1 MyField
FROM MyTable AS Dupe
WHERE ((DupeID.ID < MyTable.ID) AND (Dupe.MyField Is Not Null))
ORDER BY Dupe.ID DESC )

5. Check that the calculated field returns the desired value.

6. Change the query to an Update query. (Update on Query menu.)
Access adds an Update row to the grid.

7. Move the calculated expression into the the Update row under MyField.

8. Run the query.

This kind of calculated field is called a subquery.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Bill Clark" <wi***********@ delphi.com> wrote in message
news:be******** *************** ***@posting.goo gle.com...

I have about 20,000 records pulled from Excel that I need to update.
What I need to do is run an update query that bascially says: If a
field is null, update it with the previous record value of that same
field. In some instances, it will have to go back a few records
before it finds a value that is not null. Can this be done?

Thanks

Bill

[Keith Wilby]

"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

4. In the Field row, enter this calculated field:
( SELECT TOP 1 MyField
FROM MyTable AS Dupe
WHERE ((DupeID.ID < MyTable.ID) AND (Dupe.MyField Is Not Null))
ORDER BY Dupe.ID DESC )

I think "WHERE ((DupeID.ID < MyTable.ID)" should read "WHERE ((Dupe.ID <
MyTable.ID)"

Regards,
Keith.

www.keithwilby.org.uk

[Bill Clark]

Keith Wilby <ke*********@Aw ayWithYerCrap.c om> wrote in message news:<Xn******* *************** **@10.15.188.42 >...

"Allen Browne" <Al*********@Se eSig.Invalid> wrote:

4. In the Field row, enter this calculated field:
( SELECT TOP 1 MyField
FROM MyTable AS Dupe
WHERE ((DupeID.ID < MyTable.ID) AND (Dupe.MyField Is Not Null))
ORDER BY Dupe.ID DESC )

I think "WHERE ((DupeID.ID < MyTable.ID)" should read "WHERE ((Dupe.ID <
MyTable.ID)"

Regards,
Keith.

www.keithwilby.org.uk

Thanks! Worked great!