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
|
|||
|
|||
Problem with Update Query Returning Message Re Single Row Update
I've got a table I'm trying to update with data from another table.
The table to be updated has multiple columns but I'm concerned with two: DEPT OLDACCTNUMBER There may and probably will be duplicate values across these columns but not for a specific dept/oldacctnumber combination. 100 5000 110 5000 200 6000 210 6500 300 5000 I'm trying to update it from a table with the following columns: OLDACCTNUMBER DEPT NEWACCTNUMBER There are no duplicates when you look at OLDACCTNUMBER and DEPT combined. But there are certainly duplicates within OLDACCTNUMBER and NEWACCTNUMBER. For example, here's sample data: OLDACCT DEPT NEWACCT 5000 100 75000 5000 110 95000 5000 300 75000 I'm joining DEPT in both tables and OLDACCTNUMBER in both tables. I'm getting the following message: "Single-row update/delete affected more than one row of a linked table. Unique index contains duplicate values." I'm not sure what the issue is. -- Charles Allen, MVP |
#2
|
|||
|
|||
Problem with Update Query Returning Message Re Single Row Update
On Tue, 5 Dec 2006 21:28:01 -0800, Charles Allen
wrote: I'm joining DEPT in both tables and OLDACCTNUMBER in both tables. I'm getting the following message: "Single-row update/delete affected more than one row of a linked table. Unique index contains duplicate values." Do you have a unique Index on the two fields? Otherwise Access will not be able to determine that only one row will be affected. Open both tables in design view, and either ctrl-click both DEPT and OLDACCTNUMBER so they're both selected, and click the Key icon to make them a joint primary key; or use the Indexes tool to create a unique two-field index. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|