Re: Update - fill blank fields based on match
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Tue, 24 Jun 2008 12:14:29 +0800
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
.
- Follow-Ups:
- Prev by Date: Re: if statements in queries
- Next by Date: Re: Update - fill blank fields based on match
- Previous by thread: Query as data source for form
- Next by thread: Re: Update - fill blank fields based on match
- Index(es):
Relevant Pages
|
