How to avoid duplicate entries

[r. howell]

I have a table where the "natural" primary key would be a combination of four
fields. And for a while, we worked with it that way. Then I became
convinced that a meaningless autonumber primary key would be better, and I
reconfigured the system to work that way.

But how do I now keep people from making duplicate entries, ie. entries
where that natural combination of four fields is already in the system? If I
can lock out such entries that would work, but I'd settle for a warning
message. Thanks.

 

[Jamie Fallon]

Well do you want to enforce it through the database? If so, why did you move
away from the natural key? If you want the autonumber, but still want to
enforce the natural key, I don't remember how to do that.
Or do you want to enforce it in the user interface? If so, it all depends on
what your interface is.

 

[Craig Alexander Morrison]

Create a multiple field unique index in the table to sit alongside the
Primary Key.

This will ensure that the natural key is maintained.

Open the indexes dialog box and enter an IndexName say NaturalKey then on
the next column enter the first of the fields involved and move to the next
row and enter the next field until done. Back on the first row you can
define the properties of the Index set Unique to Yes, leave Primary as no.

You should also ensure that all of the fields that are part of the Natural
Key are Required and No Nulls are allowed.

 

[Allen Browne]

1. Open the table in design view.

2. Open the Indexes dialog (View menu.)

3. On a fresh line in the dialog, enter a name (any name) in the first
column, and select the first field in the second column. In the lower pane
of the dialog, set Unique to Yes.

4. On the next line of the dialog, leave the Name column blank, but select
the 2nd field. Repeat for the 3rd and 4th fields on subsequent lines.

5. Save.

You have now created a Unique index on the combination of the 4 fields.
Assuming the Required property is also set for these 4 fields, there will be
no dupliates in created in the future (though there might be some existing
ones.)

 

[r. howell]

Well do you want to enforce it through the database? If so, why did you move
away from the natural key?

Because I don't want to have to save all four fields in every other table
that refers to that specific record. I also want to be able to change one of
those four fields (to tell me that the record is now cancelled, but the
information needs to be saved), without losing all the other information.

If you want the autonumber, but still want to

 

[r. howell]

I had trouble getting this to work, because it told me there would be
duplicate entries that would have to be deleted. Is it just picking up the
duplicates (there are probably a few in the system) of all four fields--ie.
real duplicates? Or is it looking at each individual field and saying,
"There are a lot of Change orders with number 3 here", in which case, well,
of course there are. A change order number 3 for almost every individual
contract. What I want to avoid is multiple change order 3's for the same
contract.

Again, and I apologize for being dense, the instructions here should only
result in the requirement that the COMBINATION of all four index fields is
unique?

Thanks.

 

[Allen Browne]

The 4-field index is picking up the duplicates on the combination of all 4
fields.

If some fields may contain a Null, and you don't want that to be considered
a duplicate, set the Ignore Nulls property to Yes in the lower pane of the
Indexes dialog.

To locate the real duplicates (ignoring nulls):
1. Create a query into this table.

2. Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 4 fields into the grid.
Accept Group By in the Total row under these fields.

4. Drag your autonumber field into the grid.
In the Total row, choose Count
In the Criteria row, enter:

1

5. (Optional) Drag the AutoNumber field into the grid again twice more.
In the Total row, choose Min under the first, and Max under the second.
This shows the lowest and highest autonumber for the duplicates.

 

[Tim Ferguson]

Well do you want to enforce it through the database?

Because that is what databases are for. Otherwise, we may as well all be
using tables in MS Word. Much easier formatting...

If so, why did you move away from the natural key?

Hmm: that's a fair question.


Tim F

 

[r. howell]

OK. I figured out what I was doing wrong in creating the multiple-field
index. I had misunderstood about how to create it, and was naming each line
(despite your very clear instructions).

I've managed to create a "find duplicates" query and get rid of all the real
duplicates.

But one of my fields that is now part of the multiple field index, in a
change since the days when this was my naturally defined multiple primary
key, consists largely of nulls. In fact, there are only about 5 records out
of over 1000 where this field is not null.

I suspect that this is what is now messing me up. The index saves just
fine, but when I go to try to enter a duplicate, the system happily accepts
it.

This is true whether or not I say "ignore nulls" but my concern is that if I
tell it to ignore nulls, I'm getting the full record ignored in cases where
one field is null.

The field that is currently primarily nulls is an Alpha field. In a very
few cases, due to demands of people outside of our company, a change order
needs to have a letter attached to it, ie, we have a 3A, 3B and 3C. That's
not our normal policy. If turn my regular change order number to a text
field, so that it will accept letters, the computer counts 1, 10, 11...2,20,
etc. when I want to print out a numbered list of change orders for a job.

Do I have to put a default value like W into the field so that it won't be
null, and then tell reports not to print W but to print every other value for
the Alpha field?, or switch W to null with a query?

Thanks so much for all of this help.

 

[r. howell]

Got it. Thanks for all the help.

OK. I figured out what I was doing wrong in creating the multiple-field
index. I had misunderstood about how to create it, and was naming each line
(despite your very clear instructions).

I've managed to create a "find duplicates" query and get rid of all the real
duplicates.

But one of my fields that is now part of the multiple field index, in a
change since the days when this was my naturally defined multiple primary
key, consists largely of nulls. In fact, there are only about 5 records out
of over 1000 where this field is not null.

I suspect that this is what is now messing me up. The index saves just
fine, but when I go to try to enter a duplicate, the system happily accepts
it.

This is true whether or not I say "ignore nulls" but my concern is that if I
tell it to ignore nulls, I'm getting the full record ignored in cases where
one field is null.

The field that is currently primarily nulls is an Alpha field. In a very
few cases, due to demands of people outside of our company, a change order
needs to have a letter attached to it, ie, we have a 3A, 3B and 3C. That's
not our normal policy. If turn my regular change order number to a text
field, so that it will accept letters, the computer counts 1, 10, 11...2,20,
etc. when I want to print out a numbered list of change orders for a job.

Do I have to put a default value like W into the field so that it won't be
null, and then tell reports not to print W but to print every other value for
the Alpha field?, or switch W to null with a query?

Thanks so much for all of this help.