Re: Duplicate Records in tables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Randy (randyb_at_att.net.nots.pam)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 03:41:48 GMT

Infected04 wrote:

> I need to disallow duplicate records entered for a number field BUT allow
> the number 0 to be duplicated
> only 0
> can this be done?

Infected04, Yes, there is a way, but it is not advisable, since there are
two (2) important draw-backs:
1) You will not be able to enforce data entry on the specified field at the
table-level.
2) There will be many considerations when handling updates and reading on
queries, forms, reports, exports, and any other operations involving the
field.

Since zero (0) is not an actual number, then proceed as follow:

Set the "Required" property of the number field to "No."
Set the "Default Value" property of the field to "Null."
Set the "Indexed" property of the number field to "Yes - No Duplicates."

This will allow your table to have many records, with non-zero numbers,
while preserving the rule of not allowing duplicates.

[Considerations for data entry:]
For a numbers (different than 0) update the field.
For the number zero (0) do not update, or update the field with Null

[Considerations for data update:]
For a new number (different than 0) update the field.
For the number zero (0) update the field with Null

[Considerations when dealing with the field on queries, textboxes, etc.:]
Convert the field to a number using a field calculation, like in :
Val(nz([Field1],""))

-Randy

Relevant Pages

  • Re: Delete Row meeting a Criteria
    ... Sounds like "DUP" is the result of a formula looking for duplicates. ... If so why not change this to 0 (zero) and sort by that column, and then delete the rows with 0? ...
    (microsoft.public.excel.programming)
  • Re: random numbers
    ... But I need to insure that there are no duplicates in the set. ... Anyone have a suggestion for an easy way to do that without derandomizing the set? ... For each random number you generate, use it as an index into the array: if the corresponding array element is zero, set it to one; if the corresponding element is already one, then discard that number and generate another. ...
    (comp.lang.php)
  • Re: Having difficulty understanding SMALL function in formula
    ... and block array entered, but it still needs work to remove the duplicates, ... (there's no email, no snail mail, but somewhere should be gmail in my addy) ... be zero since $C$1:$C$14=C1 is FALSE for these rows? ... the original spreadsheet. ...
    (microsoft.public.excel)
  • Re: random numbers in fortran
    ... Watch out for duplicates :-) ... Try the Knuth shuffle instead, ... believe this gives random ints between zero and fifty-one. ...
    (comp.lang.fortran)