Results 1 to 10 of 10
  1. #1
    abublitz is offline Novice
    Windows 10 Access 2003
    Join Date
    Nov 2016
    Posts
    6

    Part Number Log with Multiple Ranges

    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.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    See this thread for links to learn about table design, structure, etc. It is a good place to start : https://www.accessforums.net/showthread.php?t=65906

    In database normalization, you have one table to contain one thing. So you would have an assembly table (can't see how you consider this to be "massive", you only have 7 listed above). It would contain:
    assembly as the primary key (3 char?), and length of key (2 or 3 or ..)

    Another table to contain all the parts, and including the assembly which links back to the assembly table.

  3. #3
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Further to aytee11's post:

    All the ranges in one table is a worthy solution.

    tblPartNumberSeries
    --------------------
    PartNumberSeriesID, PK
    PartNumberPrefix, Text
    PartNumberDesc, Text
    FromNumber, Number
    ToNumber, Number (in code include check if number is reached - if so, no more numbers available!)
    LastAssignedNumber, Number
    DateRetired, Date/Time (in case a series is ever dropped from production)

    tblParts
    ---------
    PartID, PK
    PartNumberSeriesID, FK
    PartNumber, Number
    DateAssigned, Date/Time
    SDPReleased, ?
    Rev, ?
    Status, ?
    Description, ?
    RequestedBy, ?
    ECONumber, ?


    The question marks indicate not enough information to provide data type, which I'm sure you will be fine with.

  4. #4
    abublitz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    6
    Sorry Aytee, I didn't explain well enough.

    I have around 60 ranges, the ones I provided above are only a few of them to use as an example. Each range currently has its own excel workbook, so I have 60+ workbooks to manage these ranges currently.

    As an example, 330XXX is the range for our Manufacturing Work Instructions. The first entry is PN# 330000 and, as of today, there has been entries made up to PN# 330798. PN# 330799-330999 are still available for use.

    Essentially, if I combined everything into one table, it would potentially ranged from the very first PN# which is 10175 to 900999. That's pretty massive to me.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That is the parts table, not the assemblies table. Yes it is quite large but still manageable, it all depends on you designing it with intellisense (not leaving field sizes to the default, having a correct key structure). I suggest that you design the database and get it working, then transfer the tables to SQL Server as the back-end. Or else start there.

  6. #6
    abublitz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    6
    I started creating a database and imported my first Range of data.

    I'm trying to work through the code I would need to create new part numbers.

    The process I'm thinking about is:

    Select series ID in cboPartNumberSeriesID
    Click cmdNewNumber
    Searches for PartNumberSeriesID in the tblPartNumberSeries
    Checks to see if the DateRetired is null
    Checks to see if ToNumber > LastAssignedNumber+1
    Returns LastAssignedNumber+1 in txtPartNumber (hidden on form)
    Inputs [PartNumberPrefix] & [txtPartNumber] into txtPartNumberDisplay (most just for the users benefit, will not be added to the table)

    I also think I will need to do an update query on AfterUpdate for txtPartNumber so that the LastAssignedNumber gets updated in the table. Or is there a better way to do this?


    Does this sound right?

    Thanks for all the assistance so far!

    PartNumberLog.zip

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do your analysis, build a data model, test the model with sample data.
    When that's working as expected, then build your database.

    Sort of like having a blueprint to build your house......

    The question marks by jwhite signify not enough info. If you have more info/details, then tell readers or show us the specification.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Going well.

    You wouldn't carry the last assigned number on the table, that is a calculated field and changes all the time (also is a pain to keep in sync!). You can have a standard query with the max number for that series which you use whenever you need that field. Use a DLookup to get the max number from tblParts.

    You have a timing problem here which needs to be sorted out carefully. If you add the new number straight away, then they may want to cancel the operation and that number will either be ununsed or must be deleted. Or have a Save button for the end of the process, in which case you will have to re-check the max number used and maybe it will change. If they cancel the operation but someone else has added one in the meantime, what to do with the holes in the sequence? e.g. User 1 hits new, part 123. User 2 hits new, part 124. User 1 hits cancel - now sequence is:122,124 - are you going to re-use 123?

  9. #9
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    The reason to carry the last assigned number is so someone else coming a split second later to get another number won't get the same number. This would run faster than running a DMax query every time on the primary table. DLookup is slow. The next number is grabbed only when the user confirms to commit the data and the sequence table is immediately updated.

  10. #10
    abublitz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    6
    Sorry Orange, I think I used the wrong terminology. I'm currently just laying out the tables and working on the code. The data I imported is being used as sample data to see how it all interacts.

    I am not as concerned about Un-Used numbers (I know I should be), the reasoning is, I already have some gaps in the numbers. For the life of me I will never figure out the thought process of my predecessor lol As long as that number has not been on any released documentation or entered into our MRP system, it would be acceptable to use it. Being that the ranges are limited, it would be wasteful not to use all the numbers if possible. I do realize that this is poor database use, as those empty lines are wasted data space, but I need to work with what I have. 99% of the time, I will be the only user creating new numbers so I'm not really worried about two users potentially pulling the same number.

    I had an idea about how to potentially handle this. I can create a subform that is opened with the cmdNewNumber, which will give the user the option to chose between creating a new number or selecting from a list box of Un-Used numbers. If the user selects one of the Un-Used numbers it will open that record on the main form. To determine which are Un-Used numbers, I can do a query by the Status field, which has the options of Active, Inactive, Obsolete and Un-Used.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hiding Part of A Number?
    By euphonium01 in forum Programming
    Replies: 3
    Last Post: 06-24-2014, 02:08 AM
  2. Same Part number, two different vendors
    By MFS in forum Programming
    Replies: 2
    Last Post: 02-19-2013, 10:35 PM
  3. Replies: 1
    Last Post: 01-10-2012, 10:12 PM
  4. Replies: 2
    Last Post: 04-13-2011, 09:42 AM
  5. Multiple Part Number Look=up
    By nywi6100 in forum Reports
    Replies: 0
    Last Post: 09-27-2006, 11:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums