Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > Computer Information > Restricting Duplicates in an Access Database Table

Reply
Thread Tools

Restricting Duplicates in an Access Database Table

 
 
John
Guest
Posts: n/a
 
      07-05-2006
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


 
Reply With Quote
 
 
 
 
Joseph Meehan
Guest
Posts: n/a
 
      07-05-2006
John wrote:
> 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?

--
Joseph Meehan

Dia duit


 
Reply With Quote
 
 
 
 
Graham Mandeno
Guest
Posts: n/a
 
      07-05-2006
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

"John" <AllezLesBleus@PortugalLesFraudesDePlongée.com> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
John
Guest
Posts: n/a
 
      07-09-2006
On Wed, 05 Jul 2006 23:48:02 GMT, "Joseph Meehan"
<(E-Mail Removed)> wrote:

>John wrote:
>> 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?


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

John


 
Reply With Quote
 
John
Guest
Posts: n/a
 
      07-09-2006
On Thu, 6 Jul 2006 11:48:38 +1200, "Graham Mandeno"
<(E-Mail Removed)> wrote:

>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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
home network but restricting childrens access to the web =?Utf-8?B?YW5keSBi?= Wireless Networking 4 07-26-2005 12:31 PM
Restricting User access to Router with TACACS Freeware Frank Beider Cisco 3 10-20-2003 04:39 AM
Restricting access for PPTP users to specific protocols Rik Bain Cisco 1 10-18-2003 11:48 AM



Advertisments