Re: Update - fill blank fields based on match

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????

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <John@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9ACFFAC2-4C24-4F38-9D6A-47245F53CB52@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Update - fill blank fields based on match
    ... you would not want to design a table this way. ... 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. ... UPDATE Table1 ... then look at the first rown in the second column then update the blank rows. ...
    (microsoft.public.access.queries)
  • Re: Update - fill blank fields based on match
    ... update query rather than troll through the data manually and update it. ... If you want to do it anyway, you could use a subquery to look up the first ... UPDATE Table1 ... the second column only has data in the ...
    (microsoft.public.access.queries)
  • Re: Combo Box Bug: Run-time error 3075
    ... Since you have a thorough grasp of my design, ... of tblProfilesAssociations having a second column (2nd column ... If, for example, your second column of tblProfilesAssociations ...
    (microsoft.public.access.formscoding)
  • Re: How to prevent duplicate entries
    ... user has already saved the record once, that saving a second time would only ... run an Update query, ... and use the Edit method rather than the AddNew method. ... And design the properly normalized tables FIRST, ...
    (microsoft.public.access.formscoding)
  • Re: Update current record information to a separate record status reco
    ... Take a look at an Update Query in Access. ... My original design suggestion still holds. ... but creating a "summary record" in a separate table only introduces the ... of synchronization with what is contained in your "details" table. ...
    (microsoft.public.access.forms)