Restricting Duplicates in an Access Database Table

[John]

Hi. If I have a database with a table, and on one of the fields I
restrict duplicates so it won't allow a number to be keyed in that is
already exists in a different cell in the same field. If I was to have
a spreadsheet of data that I wished to copy over and past into the
table, if it contained any duplicates for that field, would it simply
copy over everything except those duplicates, and just put the ones
that were duplicates into a paste errors table? Or would it still
allow me to paste all the data from excel? Would it only prevent
duplicates for data that was keyed directly into the table or form for
the table?

Cheers

John

 

[Joseph Meehan]

Hi. If I have a database with a table, and on one of the fields I
restrict duplicates so it won't allow a number to be keyed in that is
already exists in a different cell in the same field. If I was to have
a spreadsheet of data that I wished to copy over and past into the
table, if it contained any duplicates for that field, would it simply
copy over everything except those duplicates, and just put the ones
that were duplicates into a paste errors table? Or would it still
allow me to paste all the data from excel? Would it only prevent
duplicates for data that was keyed directly into the table or form for
the table?

Cheers

John

"different cell in the same field."

I am not sure what you mean there. Do you mean a different field in the
same record or the same field in a different record?

 

[Graham Mandeno]

Hi John

It will prevent duplicates from being created for that field, no matter what
the method of data entry. (The same applies to other constraints such as
referential integrity, validation rules, incongruent data types and
"required" fields.)

If there is anything "wrong" with the record that is being imported then the
entire record will be omitted from the import. I'm not sure whether it is
simply discarded or whether it gets written to a "Paste Errors" table.

I prefer to avoid the problem by importing to a temporary table that does
not have any restrictions. Then this data can be "pre-processed" to check
for any problems and then be imported into the real table after they are all
resolved.

 

[John]

"different cell in the same field."

I am not sure what you mean there. Do you mean a different field in the
same record or the same field in a different record?

Yes sorry, I meant the same field in a different record.

John

 

[John]

Hi John

It will prevent duplicates from being created for that field, no matter what
the method of data entry. (The same applies to other constraints such as
referential integrity, validation rules, incongruent data types and
"required" fields.)

If there is anything "wrong" with the record that is being imported then the
entire record will be omitted from the import. I'm not sure whether it is
simply discarded or whether it gets written to a "Paste Errors" table.

I prefer to avoid the problem by importing to a temporary table that does
not have any restrictions. Then this data can be "pre-processed" to check
for any problems and then be imported into the real table after they are all
resolved.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks. I will have to give this a try next week by changing it to
prevent duplicates. Hopefully it should keep a record of the ones it
has prevented being pasted into the paste errors table so we can see
which ones were the problem.

Thanks

John