Developer Forum

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

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://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.

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

Regards,
Keith.

www.keithwilby.org.uk

Thanks! Worked great!