Update query - fill in null field with value from record above

[Laura C.]

I had to do a messy data import from Excel that has left me with a column
with several thousand blank fields. I'd like to figure out a way to populate
those fields using an update query.

The table (tblActiveNAs) has four columns:

NameField
Title
FirstElected
PositionEndDate

It's the NameField that's missing data. For example:

ID NameField Title FirstElected PositionEndDate
1 JOE VP 3/4/2007
2 SVP 3/4/2009 8/4/2010
3 VP 5/4/2009
4 WILL VP 3/1/2006

I'd like to populate the null fields in NameField with whatever the entered
value above is -- i.e., the NameField would be "JOE" for records 2 and 3.
It's okay if some of the PositionEndDate fields are null.

I tried to build an update query using some sample code that I found here,
but it's not working. What am I doing wrong?

UPDATE tblActiveNA, tblActiveNA AS tblActiveNA_1 SET tblActiveNA_1.NameField
= tblActiveNA.NameField
WHERE (((tblActiveNA_1.NameField) Is Null) And
((tblActiveNA_1.ID)=tblActiveNA.ID+1));

Thanks in advance --

 

[Laura C.]

Just as a side note, I'm aware that what I'm trying to do is problematic in
terms of creating a table with a lot of duplicate information. I tried using
the "Analyze Table" tool to split off the NameField and make it a separate
table with unique records, but was obligated to verify in over 1000 cases
that the imported records should be left as is. I'm prepared to split off
table once I can get my NameField populated.

 

[John Spencer]

This MIGHT work. It relies on the ID field being sequential. You will
have to run it multiple times as the first time it will update record Id
2, but not record Id 3.

BEFORE you attempt to do this, BACKUP your data. If this does not work
as you wish, there will be no way to recover other than using a backup
of the data to restore the data.

UPDATE tblActiveNA as A INNER JOIN tableActiveNA as B
ON A.ID = B.ID-1
SET B.NameField = [A].[NameField]
WHERE B.NameField is Null and A.Namefield is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Laura C.]

Worked like a dream. I redid the import and deferred some other data clean-up
so that the ID field was in fact sequential. I just had to re-run the update
query a few times before all the blank fields were filled in.

Thank you, you are a genius.

This MIGHT work. It relies on the ID field being sequential. You will
have to run it multiple times as the first time it will update record Id
2, but not record Id 3.

BEFORE you attempt to do this, BACKUP your data. If this does not work
as you wish, there will be no way to recover other than using a backup
of the data to restore the data.

UPDATE tblActiveNA as A INNER JOIN tableActiveNA as B
ON A.ID = B.ID-1
SET B.NameField = [A].[NameField]
WHERE B.NameField is Null and A.Namefield is not Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I had to do a messy data import from Excel that has left me with a column
with several thousand blank fields. I'd like to figure out a way to populate
those fields using an update query.

The table (tblActiveNAs) has four columns:

NameField
Title
FirstElected
PositionEndDate

It's the NameField that's missing data. For example:

ID NameField Title FirstElected PositionEndDate
1 JOE VP 3/4/2007
2 SVP 3/4/2009 8/4/2010
3 VP 5/4/2009
4 WILL VP 3/1/2006

I'd like to populate the null fields in NameField with whatever the entered
value above is -- i.e., the NameField would be "JOE" for records 2 and 3.
It's okay if some of the PositionEndDate fields are null.

I tried to build an update query using some sample code that I found here,
but it's not working. What am I doing wrong?

UPDATE tblActiveNA, tblActiveNA AS tblActiveNA_1 SET tblActiveNA_1.NameField
= tblActiveNA.NameField
WHERE (((tblActiveNA_1.NameField) Is Null) And
((tblActiveNA_1.ID)=tblActiveNA.ID+1));

Thanks in advance --