• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

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 Shukor
Yann Shukor
4 Solutions
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.
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 sshon7782@gmail.com 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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now