Avoiding duplicates in tables

[dawnecia]

I have a table with ID (auto number) as the primary key (A97 – multi-user
environment). There should only be one entry per User ID per day. I am
currently getting duplicates (users are creating a more than one entry per
day). I have tried to create indexes on the user id and the date fields with
no luck. I have tried using a event in the after update of the date field on
the form and can not get the code to work. This is how the form works – the
user inputs the user id then the date then fills out the form. What I would
like is after the date is updated an event runs that checks if this new
record is a duplicate. Any suggestions?

 

[Rick B]

While in design view on the table, click the USERID field. Hold down CNTRL
and then click the DATE field. Both should now be highlighted. Now, click
the PRIMARY KEY icon at the top of the screen. This creates a compound key.

Now, click from the menu VIEW/INDEXES. This will allow you to set the
UNIQUE value to true.

Rick B

 

[Rick B]

Oops. Disregard the part about setting it to unique. A primary key (by
definition) must be unique.

Rick B

 

[Rick Brandt]

I have a table with ID (auto number) as the primary key (A97 - multi-user
environment). There should only be one entry per User ID per day. I am
currently getting duplicates (users are creating a more than one entry per
day). I have tried to create indexes on the user id and the date fields with
no luck. I have tried using a event in the after update of the date field on
the form and can not get the code to work. This is how the form works - the
user inputs the user id then the date then fills out the form. What I would
like is after the date is updated an event runs that checks if this new
record is a duplicate. Any suggestions?

You don't want separate indexes on the two fields. You want a single index that
uses both fields combined and then you also set it to be "unique". As long as
you store only the date and not the time (actually stores midnight) that will
prevent any duplicates on the combination of those two fields.

Making composite indexes is not real obvious in table design view. You have to
open the indexes dialog from the "View" menu and you add a new one by typing
only one new index name along with one of your field names on one row and in the
row immediately beneath that you type the name of the second field leaving the
index name blank. At the bottom is the option to make the index unique.