Inserting Records on an Indexed Column No Duplicates

[Miked811]

Hi Guys,

I have an access table that has an index column. This table is used to store all the "dialed" phone #s provided by the "client" to call, as in telemarketing phone #s. Lets call it CalledTbl

structure below:

CalledTbl
---------
Phonenum - Index: (Yes No Duplicates)


At the end of the month the client sends us another set of phone #s. now, some of these phone #s could be numbers that has already been called from the previous months. whatever number we call at the end of the day, it goes into CalledTbl.

Inserting the records manually in access gives me this error message:

"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."

Then it ask me if I want to proceed. I click Yes, the records that does not exist in the CalledTbl gets inserted, the once that already exist get discarded.

I want to automate the process. I get the same error message using ASP after trapping the error using, On Error Resume Next...On Error Goto 0. But, the records never gets inserted unlike doing it manually...this is the problem.

Please help...

thanks a lot....

 

[JerryKlmns]

Let 's say you 've got a new list of phone numbers from your clients in the table NewPhones with the field Phonenum. Then you need to append only non-existing ones in table CalledTbl.
Something like :

INSERT INTO CalledTbl(Phonenum)
SELECT NewPhones.Phonenum
FROM NewPhones LEFT JOIN CalledTbl ON
NewPhones.Phonenum = CalledTbl.Phonenum
WHERE CalledTbl.Phonenum Is Null

 

[GHTROUT]

The way I perform similar additions is to enter the new records in a different table with the same structure but without the "no duplicates" or primary key field. Then perform an append query into the master table, it will at least only warn you once - and I think "Set Warnings=Off" may work in that case too..

~

http://www.GHTROUT.com/

 

[Miked811]

thanks...i'll work on your suggestions...cheers....

 

[barny2006]

you can also use the NOT EXIST option to check to see if the phone number exists or not, and insert, only if it NOT EXIST.