If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Avoiding duplicates in tables
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? |
#2
|
|||
|
|||
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 "dawnecia" wrote in message ... 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? |
#3
|
|||
|
|||
Oops. Disregard the part about setting it to unique. A primary key (by
definition) must be unique. Rick B "dawnecia" wrote in message ... 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? |
#4
|
|||
|
|||
"dawnecia" wrote in message
... 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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Is there a way to check for duplicates between tables in a databa. | K.W. | Running & Setting Up Queries | 1 | November 3rd, 2004 10:20 PM |
how can I find duplicates in two tables based on phone number? | dustinw | Running & Setting Up Queries | 2 | October 5th, 2004 12:36 AM |
Linking two Tables and avoiding having to create an interim Query | John Ortt | Running & Setting Up Queries | 5 | September 29th, 2004 06:51 PM |
Avoiding importing duplicates or deleting them | Tom in Seattle | Calendar | 0 | September 28th, 2004 07:45 PM |