Part numbers with duplicate descriptions using combo box

[Xdreamer]

I consider myself somewhat knowledgable with Access 2010 but not a programmer. Please help me
I have a table built with part numbers and short descriptions, because of part number changes I have some descriptions that are duplicate.
I created a form with a combo box so that when the data entry person enters the part number the description auto appears and is stored in the table.
When a part number is entered that happens to have a duplicate description the other duplicate part number shows up.
For instance I want to type in PN: 3000-3022-00 and I want G_1.00_090 to appear in the description


When I type 3000-3022-00 and click tab the correct description appears but with the other part number for example 1414-1000-0005-10

Can someone please tell me what I need to do to stop this from happening?

[June7]

If the part number is typed, why in heck would a different part number show?

The part number is selected for what purpose - as part of a customer order or inventory purchase? Why save the description to this record? Just save the part number that is selected.

Want to provide db for analysis? Follow instructions at bottom of my post.

[Xdreamer]

This form is a detailed lookup form to show what type of system was purchased and sold to a customer so I want the part number that was originally shipped to them to show up in the form but not in the table that is why I am only saving the description to the table. This way if a new employee looks at the db they will know what the item is by the description. This also prepares us for future part number changes if that happens again.

[June7]

Sounds like GIGO (garbage in garbage out) syndrome due to bad design. Doing a lookup will return the first part number found that matches the description. Need to save the part number. Can always retrieve the related description by joining the tables.

The other part number shows up where? What code is retrieving the description and associated part number?

We can keep going in circles or you can provide the db so I can directly analyse.

[Xdreamer]

I didn't design the db and your correct it is a bad design but I am trying to work with what we have instead of re-entering 16 years of data. The db was not originally set up correctly as all information is saved in one large table. It's like a big excel spreadsheet. This is why I won't send you the db plus it would be against my companies policy. Thanks for the help but not for the attitude

[June7]

Sorry my humor fell flat. Without seeing form structure and code, don't think can help much. Don't care about actual data, a few dummy records for testing would be enough.

The reality is it is bad design and will continue to give you headaches unless it is fixed. If there are multiple part numbers that can match to one description, what criteria would determine the one you want? The latest one created? Is there a date or an autonumber field associated with the part? Or the part number where the first 4 digits compose the largest number?