Hello all,
I'm looking for some advice on how to set up this database and if I should even do it. I'm not overly experienced in access but if pointed in the right direction, I can usually figure stuff out.
Some backstory here, I inherited the Document Control position at my job from a lady who had been here for 20+ years. What I am working with is established with my company and I can't really change the part number schemes. I know they are not ideal and that specific ranges opens up the possibility of running out of numbers.
So my company has a part numbering scheme where different types of parts or documents fall into a different range.
Example:
19XXXX - Assemblies
205XXX - Component, Aluminum Capacitors
206XXX - Component, Ceramic Capacitors
230XXX - Component, Transformers
801XXX - Forms
335XXX - Departmental Work Instructions
390XXX - SOPs
There are 60+ of these ranges and currently each range is in its own excel workbook. It is not fun maintaining that many workbooks as generally when I'm processing changes, I have to have several of them open.
The info contained in the workbooks is fairly simple with the following columns: Part Number, Date Assigned, SDP Released, Rev, Status, Description, Requested By, and ECO#.
I was thinking that perhaps I could combine all these into one database with the goal of being able to assign the next available part number in the designated range and to have the ability to update a record when needed. I am a little torn as to how to accomplish this though.
At first I was thinking that I would make a table for each range but I am unsure how to, or if its even possible, using one form to switch between tables. I suppose I could make a table/form for each range and then on my switchboard I could add a combo box where you select which range you need and it will bring up that specific form... that seems kind of excessive though.
The other thing I was thinking about was combining all the ranges into one table, the downside to this is that the table would be absolutely massive.
Suggestions are greatly appreciated.