Re: Update - fill blank fields based on match

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
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:11A7BEC0-D076-4351-97E2-EA65A3E146D8@xxxxxxxxxxxxxxxx
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
>
> "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: 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)
  • Re: Example of a append query and a update query
    ... If you want to see examples of append and update queries, ... > Can you please give me a sample of a append query and a update query. ... > I have a scenario where I have table1 and table2. ...
    (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)
  • Example of a append query and a update query
    ... Can you please give me a sample of a append query and a update query. ... I have a scenario where I have table1 and table2. ...
    (microsoft.public.access.queries)
  • Re: Other form of CTRL+APOSTROPHE ()
    ... What I have done is setup an Update query ... with criteria set to 'is null' for the field 'NSN/MODDACC'. ... This example assumes a table named Table1, with an Autonumber named ID to ... Part# Desc QTY UOM ...
    (microsoft.public.access.modulesdaovba)