Check For Duplicate

Say a record has 15 fields. Is there a better way to check for a duplicate
when entering a new record than fourteen "Ands" in the where clause of
DCount or FindFirst?

Thanks,

Jerry

re: Check For Duplicate

Umm... set the Allow Duplicates flag in the table to No, maybe, and
then set an PK index on all 15 fields. (whoa, that's ugly.)

re: Check For Duplicate

Don't think that'll work. Access only allows 10 fields in an index.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



<pietlinden@hotmail.com> wrote in message
news:1112049516.497896.103640@g14g2000cwa.googlegr oups.com...[color=blue]
> Umm... set the Allow Duplicates flag in the table to No, maybe, and
> then set an PK index on all 15 fields. (whoa, that's ugly.)
>[/color]

re: Check For Duplicate

Douglas J. Steele wrote:[color=blue]
> Don't think that'll work. Access only allows 10 fields in an index.[/color]

Didn't think so... wow, that sounds like an ugly solution...

re: Check For Duplicate

"Jerry" <jsonger@nospam.please> wrote in
news:xA%1e.7717$z.2915@newsread2.news.atl.earthlin k.net:
[color=blue]
> Say a record has 15 fields. Is there a better way to check for a
> duplicate when entering a new record than fourteen "Ands" in the
> where clause of DCount or FindFirst?[/color]

Are all 15 fields required? If not, then the index you need for
determining duplicates is actually smaller, since only the required
fields should be used for determining duplicates (Nulls can't occur
in unique indexes and still guarantee uniqueness).

I for one have never had a table where 15 fields were required for
determining uniqueness -- a natural key with that many fields
suggests a severely denormalized table structure to me.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Check For Duplicate

David,

Thanks for your wisdom!

<<only the required fields should be used for determining duplicates>>
That sure makes sense.

Jerry


"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns9627D96944B9Bdfentonbwaynetinvali@24.168.1 28.86...[color=blue]
> "Jerry" <jsonger@nospam.please> wrote in
> news:xA%1e.7717$z.2915@newsread2.news.atl.earthlin k.net:
>[color=green]
> > Say a record has 15 fields. Is there a better way to check for a
> > duplicate when entering a new record than fourteen "Ands" in the
> > where clause of DCount or FindFirst?[/color]
>
> Are all 15 fields required? If not, then the index you need for
> determining duplicates is actually smaller, since only the required
> fields should be used for determining duplicates (Nulls can't occur
> in unique indexes and still guarantee uniqueness).
>
> I for one have never had a table where 15 fields were required for
> determining uniqueness -- a natural key with that many fields
> suggests a severely denormalized table structure to me.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]