Restricting Duplicates in an Access Database Table

Discussion in 'Computer Information' started by John, Jul 6, 2006.

  1. John

    John Guest

    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
    John, Jul 6, 2006
    #1
    1. Advertising

  2. 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
    Joseph Meehan, Jul 6, 2006
    #2
    1. Advertising

  3. 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:...
    > 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
    >
    >
    Graham Mandeno, Jul 6, 2006
    #3
  4. John

    John Guest

    On Wed, 05 Jul 2006 23:48:02 GMT, "Joseph Meehan"
    <> 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
    John, Jul 9, 2006
    #4
  5. John

    John Guest

    On Thu, 6 Jul 2006 11:48:38 +1200, "Graham Mandeno"
    <> 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
    John, Jul 9, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joachim Krais
    Replies:
    2
    Views:
    15,011
    Andre Beck
    Nov 23, 2003
  2. Guy Cox
    Replies:
    1
    Views:
    1,279
    sharon
    Jul 9, 2003
  3. Fluker
    Replies:
    0
    Views:
    1,057
    Fluker
    Jul 9, 2003
  4. DataBase DataBase DataBase DataBase

    , Sep 26, 2012, in forum: Computer Information
    Replies:
    0
    Views:
    1,010
  5. Database Database Database Database

    , Sep 27, 2012, in forum: Computer Information
    Replies:
    0
    Views:
    868
Loading...

Share This Page