Check For Duplicate

[Jerry]

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

[pietlinden]

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.)

[Douglas J. Steele]

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!)

<pi********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

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.)

[pietlinden]

Douglas J. Steele wrote:

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

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

[David W. Fenton]

"Jerry" <js*****@nospam.please> wrote in
news:xA***************@newsread2.news.atl.earthlin k.net:

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?

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

[Jerry]

David,

Thanks for your wisdom!

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

Jerry
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Jerry" <js*****@nospam.please> wrote in
news:xA***************@newsread2.news.atl.earthlin k.net:

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?

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