Re: How to avoid duplicate entries

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

"Allen Browne" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "r. howell" <rhowell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:2A537AA2-6878-407D-A604-A0D8D6FBDB36@xxxxxxxxxxxxxxxx
> >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" wrote:
> >
> >> 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.)
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "r. howell" <rhowell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:B4EDC9D4-BC13-4F38-AE3E-DE87FFEA1F12@xxxxxxxxxxxxxxxx
> >> >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.
> >>
> >>
> >>
>
>
>
.

Quantcast