General discussion


Duplicate entries

By ddevans888 ·
My Access Data base won't allow me to change the Serial number field and a couple of other fields to "NO Duplicates". I already have over a thousand records and don't want to search each field for duplicates is there a solution?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Have you tried Microsoft's Knowledebase?

by Why Me Worry? In reply to Duplicate entries

I'm not an Access expert, but have been able to find a lot of good info on Microsoft's website. After all, they do make the product, so who better than the source to ask for advice? I have an Access 2003 e-Book in PDF format which I can email to you if you like.

Collapse -

Access Ebook

by ddevans888 In reply to Have you tried Microsoft' ...

That would be awesome. Please email it to

Collapse -

Basically access is helping you out

by Tony Hopkinson In reply to Duplicate entries

It's saying you will throw some of your data away if you do this, you might not care, but it's not something you'd want to do by accident. What you do now depends on the data.
Select Distinct SerialNumber from MySerialNumbers will tell you how many would be left with no duplicates.
Select Distinct Row from MySerialNumbers would tell you how many exact column for column duplicates you have.

If the number of records reurned in the above two queries is not the same, you need to decide how to choose one row over another such as say if you had DateCreated in there you could choose to keep the most recent one. Access can't do that for you automatically though, you need to make the decision(s).

My preferred solution is to create another table with the structure required and then select into it. Once you've got all the data you need, then you can delete the original and then rename the new version back to SerialNumbers or whatever it was called.
For investigation purposes.
Select SerialNumber, Count(*) as NumberofNumbers from SerialNumbers Group By SerialNumber
Then select from that a list of serialnumbers where NumberofNumbers > 1 will give you a quick way of examining what sort of duplicates you have.

Collapse -


by ddevans888 In reply to Basically access is helpi ...

That did the trick duplicate erradicated.

Collapse -

Access Database

by Coder_For_You In reply to Duplicate entries

I think the Serila Number field has been set autonumber . which is the reason it doens not allow u to change the value.

I can do some changes in ur application and get it working for you, if u r interested.

Pls feel free to contact me.


Related Discussions

Related Forums