If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update - fill blank fields based on match
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 |
#2
|
|||
|
|||
Update - fill blank fields based on match
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/Ap****ueBadWord.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" wrote in message ... 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 |
#3
|
|||
|
|||
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/Ap****ueBadWord.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" wrote in message ... 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 |
#4
|
|||
|
|||
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/Ap****ueBadWord.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" wrote in message ... 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 |
#5
|
|||
|
|||
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" wrote in message ... 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/Ap****ueBadWord.html "John" wrote in message ... 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 |
#6
|
|||
|
|||
Update - fill blank fields based on match
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 John wrote: 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 |
Thread Tools | |
Display Modes | |
|
|