Results 1 to 7 of 7
  1. #1
    EvilPickle is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    3

    Inventroy database planning - Beginner

    Hello, I'm in the planning stages of what most people would probably consider a pretty straight forward database for inventory tracking at work. I'm very new to Access though and I would like to save myself some hassle and headaches if possible. So I'm hoping people here could let me know if my thought direction is correct and my plans should work.

    So the inventory that I have to track has Part Numbers and Part Names. Some of the parts have Serial Numbers, while most don't. Some inventory is spare parts, others are being used.

    Right now I'm tracking everything on an Excel Spreadsheet. The following is what I'm currently tracking...

    - Part Number
    - Part Name
    - Part Location
    - Quantity


    - Serial Number
    - System
    - Sub-System
    - Remarks

    Here are my issues...
    - Some equipment has two part numbers. In the system that we are using to order parts, some of the parts have a different P/N. They are working to fix this, but it's slow and for now I have to track both P/N's.
    - Like I said, some equipment has Serial Numbers, while others do not.
    - The System and Sub-Systems... While the Systems are all different, there are a couple Sub-Systems with the same names... example, ECU's. Both Systems have ECU's.. both ECU's are different and use different parts.

    Eventually I would like to expand this database to include maintenance and tracking of parts that have been broken, returned for repairs or replaced with new parts. But for now a simple inventory database would help me out greatly.

    I came up with the following for tables and relationships..
    Click image for larger version. 

Name:	Access Relationships.png 
Views:	42 
Size:	17.7 KB 
ID:	33024

    As you can see I didn't add in System and Sub-System. I would like these to be used with Combo Boxes, so I know they will need their own tables. No idea how they would connect to the others on the relationship table though.

    I believe that is all that I have for right now... Any help or direction would be greatly appreciated and a big thank you for reading.

    EP

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    From what you have described / shown, the SerialNumbers table is superfluous. It is included in Location table.

    Just use two tables. Part Numbers joined to Location
    Suggest you use Part Number as field name in both tables

    Or better to remove spaces from all field and table names as they will cause you problem later
    E.g use PartNumber, SerialNumber instead
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    EvilPickle is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    3
    ridders52, thanks for the suggestions! Makes total sense now that I think about it. I knew that I was seriously overthinking things.

  4. #4
    keviny04 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Apr 2015
    Posts
    128
    A few issues that I see:

    1. What are the relationships of systems, equipment, and parts? Is a part a "subset" of an equipment? Is an equipment a subset of a system? Any time you have something that is a subset of something, you are looking at a one-to-many relationships.

    2. You said a part can have a different part number. How many different part numbers can a part have? Are you sure it is always "a" (i.e. only one) different p/n? This info affects your table design. If you are sure it is always "a" different p/n, i.e. a part can have at most two p/n's, then you just can add a field called "alternate_part_num" to your parts table. If a part can have multiple p/n's, that means p/n's are a subset of a part, so you need, again, one-to-many relationship.

    3. You mentioned a part has a serial num, and an equipment can also have a serial num? Are those two different types of serial nums? If so, you need to design your tables accordingly.

  5. #5
    EvilPickle is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2018
    Posts
    3
    Quote Originally Posted by keviny04 View Post
    A few issues that I see:

    1. What are the relationships of systems, equipment, and parts? Is a part a "subset" of an equipment? Is an equipment a subset of a system? Any time you have something that is a subset of something, you are looking at a one-to-many relationships.

    2. You said a part can have a different part number. How many different part numbers can a part have? Are you sure it is always "a" (i.e. only one) different p/n? This info affects your table design. If you are sure it is always "a" different p/n, i.e. a part can have at most two p/n's, then you just can add a field called "alternate_part_num" to your parts table. If a part can have multiple p/n's, that means p/n's are a subset of a part, so you need, again, one-to-many relationship.

    3. You mentioned a part has a serial num, and an equipment can also have a serial num? Are those two different types of serial nums? If so, you need to design your tables accordingly.
    1. Pretty much. I have two large systems, each with different equipment and parts. Some of the equipment has the same name, but are different.

    2. For one system, the program that we use to order replacement parts has a different set of part numbers for some parts, not all. So the maintenance program tells us that the replacement part is called 'AB123' but we have it as 'XY987'.. so I have to have both part numbers in my database until the program gets updated with the correct part numbers for us to look up.

    3. Yes, both parts and equipment can have serial numbers. I wouldn't say they are different. Mostly we care about the equipment serial numbers.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    My advice is, all parts for which you have to follow serial number, i.e. you have to follow individual part/device, must be registered in PartNumbers individually - with own part number.

    The best solution will be to add a field PartGroup into table PartNumbers. For every PartGroup, you determine is this single-part or multi-part group. Serial numbers can be added to single-part group parts only, and such parts have always the quantity 1.

    So your table PartNumbers will have structure like: PartNumbers: PNID, PartGroup, PartNumber, PartName, SN, Qty.

    An additional bonus: You can design front-end form as main form-subform system, where on main form you select PartGroup, and on subform you add parts into this group, or edit parts in this group. When you have to follow hundreds or thousands of different parts, it will be helpful to split them into smaller groups.

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

Similar Threads

  1. Database planning
    By dniezby in forum Programming
    Replies: 13
    Last Post: 11-28-2018, 08:42 AM
  2. Inventroy totals
    By sprtrmp in forum Programming
    Replies: 21
    Last Post: 10-29-2015, 02:20 PM
  3. Help with Database Planning
    By dgaletar in forum Access
    Replies: 12
    Last Post: 03-21-2014, 10:31 AM
  4. Help with Table Planning in Student Database
    By whitelexi in forum Access
    Replies: 5
    Last Post: 03-09-2014, 03:08 PM
  5. Diary Planning Database?
    By Sawyer05 in forum Database Design
    Replies: 1
    Last Post: 02-16-2012, 06:27 PM

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