By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,472 Members | 2,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,472 IT Pros & Developers. It's quick & easy.

Update Query - Update field with previous field value

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
"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

P: n/a
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.