469,592 Members | 2,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

Update Query - Update field with previous field value

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
Nov 12 '05 #1
3 6492
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.google.c om...
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

Nov 12 '05 #2
"Allen Browne" <Al*********@SeeSig.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
Nov 12 '05 #3
Keith Wilby <ke*********@AwayWithYerCrap.com> wrote in message news:<Xn************************@10.15.188.42>...
"Allen Browne" <Al*********@SeeSig.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!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Dejan Pujic | last post: by
4 posts views Thread by Maxi | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.