Re: Update - fill blank fields based on match

Disregard my last.

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

"John" wrote:

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
    ... I understand this is a bad design, ... update query rather than troll through the data manually and update it. ... UPDATE Table1 ... the second column only has data in the ...
    (microsoft.public.access.queries)
  • Re: Update - fill blank fields based on match
    ... Presumably you substituted your actual table name for Table1. ... 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 ... the second column only has data in>> the ...
    (microsoft.public.access.queries)
  • 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: Re:Newbe question
    ... "Vishal Parkar" wrote in message ... > basically you'll have to fire an update query to achieve ... >>The second column contains data that I want to move to ...
    (microsoft.public.sqlserver.security)
  • Re: Other form of CTRL+APOSTROPHE ()
    ... Provided you have a primary key field in the table so that the sorting order is defined, you can execute an Update query that contains a subquery that gets the most recent non-null Part# for each row. ... This example assumes a table named Table1, with an Autonumber named ID to define the sort order: ... Part# Desc QTY UOM ...
    (microsoft.public.access.modulesdaovba)