Update - fill blank fields based on match

[John]

I have a table that is imported where two columns contain the same data for a
number of rows. Unfortunatley, the second column only has data in the first
row and the remaining are blank.

I want to run an update query to match the data from the first column and
then look at the first rown in the second column then update the blank rows.

i.e.

CODE DESC
1 AA
1
1
2 BB
2
2

I want this after the update:
CODE DESC
1 AA
1 AA
1 AA
2 BB
2 BB
2 BB

 

[Allen Browne]

In general, you would not want to design a table this way. One of the basic
rules of data normalization is that you do not have repeating rows where one
column is dependent on another. In your example, DESC is dependent on CODE,
so the table should have one or the other, not both. If you do have both,
you open the door to bad entries, where the DESC and CODE values don't match
in some rows.

If you want to do it anyway, you could use a subquery to look up the first
non-blank DESC for the CODE, and use that in an Update query. This kind of
thing:

UPDATE Table1
SET [DESC] =
(SELECT First([DESC]) AS TheValue
FROM Table1 AS Dupe
WHERE Dupe.CODE = Table1.CODE
AND Dupe.[DESC] Is Not Null
GROUP BY Dupe.CODE)
WHERE [DESC] Is Null;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

BTW, DESC is a reserved word, so not a good field name. (It's used for
descending sort in queries.) For a list of field names to avoid when
designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[John]

Thanks for that. I understand this is a bad design, but the table is
populated from another source so I am trying to fill in the blanks via the
update query rather than troll through the data manually and update it.

When I tried to use your example statement I was presented with a prompt box
for Dupe.CODE and Table1.CODE when I tried to run the query????

In general, you would not want to design a table this way. One of the basic
rules of data normalization is that you do not have repeating rows where one
column is dependent on another. In your example, DESC is dependent on CODE,
so the table should have one or the other, not both. If you do have both,
you open the door to bad entries, where the DESC and CODE values don't match
in some rows.

If you want to do it anyway, you could use a subquery to look up the first
non-blank DESC for the CODE, and use that in an Update query. This kind of
thing:

UPDATE Table1
SET [DESC] =
(SELECT First([DESC]) AS TheValue
FROM Table1 AS Dupe
WHERE Dupe.CODE = Table1.CODE
AND Dupe.[DESC] Is Not Null
GROUP BY Dupe.CODE)
WHERE [DESC] Is Null;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

BTW, DESC is a reserved word, so not a good field name. (It's used for
descending sort in queries.) For a list of field names to avoid when
designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table that is imported where two columns contain the same data for
a
number of rows. Unfortunatley, the second column only has data in the
first
row and the remaining are blank.

I want to run an update query to match the data from the first column and
then look at the first rown in the second column then update the blank
rows.

i.e.

CODE DESC
1 AA
1
1
2 BB
2
2

I want this after the update:
CODE DESC
1 AA
1 AA
1 AA
2 BB
2 BB
2 BB

 

[John]

Disregard my last.

The error I get is "Operation must use an updateable query"

Thanks for that. I understand this is a bad design, but the table is
populated from another source so I am trying to fill in the blanks via the
update query rather than troll through the data manually and update it.

When I tried to use your example statement I was presented with a prompt box
for Dupe.CODE and Table1.CODE when I tried to run the query????

In general, you would not want to design a table this way. One of the basic
rules of data normalization is that you do not have repeating rows where one
column is dependent on another. In your example, DESC is dependent on CODE,
so the table should have one or the other, not both. If you do have both,
you open the door to bad entries, where the DESC and CODE values don't match
in some rows.

If you want to do it anyway, you could use a subquery to look up the first
non-blank DESC for the CODE, and use that in an Update query. This kind of
thing:

UPDATE Table1
SET [DESC] =
(SELECT First([DESC]) AS TheValue
FROM Table1 AS Dupe
WHERE Dupe.CODE = Table1.CODE
AND Dupe.[DESC] Is Not Null
GROUP BY Dupe.CODE)
WHERE [DESC] Is Null;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

BTW, DESC is a reserved word, so not a good field name. (It's used for
descending sort in queries.) For a list of field names to avoid when
designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table that is imported where two columns contain the same data for
a
number of rows. Unfortunatley, the second column only has data in the
first
row and the remaining are blank.

I want to run an update query to match the data from the first column and
then look at the first rown in the second column then update the blank
rows.

i.e.

CODE DESC
1 AA
1
1
2 BB
2
2

I want this after the update:
CODE DESC
1 AA
1 AA
1 AA
2 BB
2 BB
2 BB

 

[Allen Browne]

Presumably you substituted your actual table name for Table1.

Be sure to include the square brackets around the table/field names,
especially DESC (or change that field name.)

Of course, you do need write access to the table you are trying to update.

You could try adding an AutoNumber to the table you are trying to update,
and mark it as primary key.

The query should be updatable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Disregard my last.

The error I get is "Operation must use an updateable query"

Thanks for that. I understand this is a bad design, but the table is
populated from another source so I am trying to fill in the blanks via
the
update query rather than troll through the data manually and update it.

When I tried to use your example statement I was presented with a prompt
box
for Dupe.CODE and Table1.CODE when I tried to run the query????

In general, you would not want to design a table this way. One of the
basic
rules of data normalization is that you do not have repeating rows
where one
column is dependent on another. In your example, DESC is dependent on
CODE,
so the table should have one or the other, not both. If you do have
both,
you open the door to bad entries, where the DESC and CODE values don't
match
in some rows.

If you want to do it anyway, you could use a subquery to look up the
first
non-blank DESC for the CODE, and use that in an Update query. This kind
of
thing:

UPDATE Table1
SET [DESC] =
(SELECT First([DESC]) AS TheValue
FROM Table1 AS Dupe
WHERE Dupe.CODE = Table1.CODE
AND Dupe.[DESC] Is Not Null
GROUP BY Dupe.CODE)
WHERE [DESC] Is Null;

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

BTW, DESC is a reserved word, so not a good field name. (It's used for
descending sort in queries.) For a list of field names to avoid when
designing tables, see:
http://allenbrowne.com/AppIssueBadWord.html

I have a table that is imported where two columns contain the same
data for
a
number of rows. Unfortunatley, the second column only has data in
the
first
row and the remaining are blank.

I want to run an update query to match the data from the first column
and
then look at the first rown in the second column then update the
blank
rows.

i.e.

CODE DESC
1 AA
1
1
2 BB
2
2

I want this after the update:
CODE DESC
1 AA
1 AA
1 AA
2 BB
2 BB
2 BB

 

[John Spencer]

UPDATE YourTable
SET [DESC] = DLookup("[DESC]","YourTable","CODE=" & Code & " AND [DESC] is not
null"

I believe that Allen Browne's solution will not work in Access, due to
limitations in the database engine. Generally UPDATE queries will not work
with an aggregate query in the SET clause or in the UPDATE clause. Also, you
cannot use a subquery in the SET clause.

This means you have to resort to using one of the VBA aggregate functions.
This can be slow with large numbers of records. If you have 100,000 records,
the alternative is to create a table with records that contain the needed
values and then use that table to update the original table.

That process would involve queries like the following.

Make Table query:
SELECT DISTINCT Code, [DESC]
INTO ValuesTable
FROM YourTable
WHERE [DESC] is Not Null

UPDATE YourTable INNER JOIN QueryMakeTable
ON YourTable.Code = QueryMakeTable.Code
SET YourTable.Desc = [QueryMakeTable].[Desc]
WHERE YourTable.Desc = Null

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County