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

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]