Problem with Insert going from Duplicates to NO Duplicates

[DrPhil]

I am having a problem with the SQL INSERT command in VB. I am trying in move data from one table to another. The problem is I am going from a table with duplicates to one with no duplicates. Since there are duplicates the SQL statement bombs. Here is the SQL code:

INSERT INTO tblAssignmentAttendWard ( fldAssignAttendingWard ) SELECT tblPatients.fldAttending FROM tblPatients)

The: tblPatients.fldAttending contains duplicate entries

The: tblAssignmentAttendWard.fldAssingAttendingWard (is a Key with NO DUPLICATES)

Here is the error:

Error Number: -2147217900

[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

The frustrating problem is that if I copy the SQL statement into a query in Access (that is the database I am using) it works fine.

HELP

[vb5prgrmr]

Select DISTINCT...

Have a look it up in help...

Good Luck

[DrPhil]

Only partially works.

If I am trying to add for example: apple, grape, bannana and peach using INSERT with a SELECT DISTINCE.

The database already contain apple and grape.

The INSERT fails and bannana and peach are NOT added.

HELP!

[ryan311]

drphil, don't set a primary key to your table.

try it and i'm sure it will work well.