MS-ACCESS assigning and managing product IDs


My supplier sends me a monthly product list which I manage in MS-ACCESS

I want to assign an identifier to each product in the supplier's product list

How can I preserve and extend this ID assignment, in MS-ACCESS, when I will receive next month's product list ?

Yann ShukorOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Create and fill a table with two fields, your ID and the supplier's ID.

Then, when linking or importing the new list, use a query to join your ID table and the new product table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
What type of file form you supplier?
CSV or Excel file: You can match your ids with supplier ids by vlookup before upload to access.
Text or Access file:
1. You have to have a table with 2 fields like Comment ID 42457715.
2. Upload supplier table in access and use query to match your ids vs supplier ids.
Samuel ShonSoftware EngineerCommented:
i will first of all like to know your table structure in ms access. in any case access create and assign ids to all items by default. you can modify this to your desire structure or change it. you can also  create it afresh by adding an AutoNumber Filed to your table structure.

let me know if this helps or email me  your table snapshot to me on and i will get it done for you.
Thank You
John TsioumprisSoftware & Systems EngineerCommented:
One more thing...your supplier probably uses an ERP application to monitor their products...maybe it would be convenient for both to use the ID assigned by the ERP to each product of your supplier either as a key (depends on your table and application structure) or as an indexed separate field...
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
John made an very important point with his comment; you must do this for each supplier, so you must include the supplier ID you use for that supplier as you might have two different suppliers using the same part number.

So you need:

SupplierID - who is this for
TheirItemNumber - what is their part number
OurItemNumber - Our item number for this

supplier         their #       Our #
ABC                1234           Widget
XYZ                 1234           Bolt, hardened steel

This is not uncommon in systems where you keep track of a customer or vendors part numbers in your system, and you may tie a lot of different things to this like pricing, placement of PO's, etc.    Also note two other things:

1. It is also not uncommon for you to have multiple entries that cross reference to the same item for you, and you need to allow for that.
2. Make sure you use a text field for their item number at the very least.   You never can tell what a vendor may use for their numbering system.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.