Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Complicated Inventory + Kits

  1. #1
    Join Date
    Apr 2019
    Location
    Minneapolis MN
    Posts
    5

    Complicated Inventory + Kits

    Hi. I've basically just started. I'm not new to Access but this is a new build. I'm looking for help from the beginning so I don't mess it up and have to go back and fix it all. This is the most complicated undertaking I've attempted, this is more than a contact list. This is a custom Lego kit company. Imagine Lego bricks sorted by type and color in bins along a wall of shelving.



    The problem now:
    Each kit has a different spreadsheet listing the parts and the required number of parts included in each kit. The team has 45,000 gazillion shared spreadsheets open per day. Excel crashes on them daily with the load.

    The Goal: Database (duh lol)

    Key Points:
    Basic inventory of Lego parts.
    Each part number (may have duplicates) has a color modifier.

    Complications:
    Each kit contains a set number of parts from the wall. I want to be able to create a query that will print the list of products so the production team can assemble the correct number of parts per kit. Each kit can contain several parts. I want this query to be concerned with the number of parts needed for the kit, and discard how many are on the wall.

    The purchaser needs to be concerned with the number of parts in the bins on the wall so he knows how many are needed to buy, and also concerned with how many are needed for the kit. (Example: We have 13 in a bin on the wall, kit needs 27 of this part and we're making a batch of 50-100 kits. Access should do the math here).

    The beginning:
    So far I have:

    Table 1: Color Code
    Color / ColorCode

    Table 2: Master Parts List
    OldPartNo / PartNo / LDrawDescription / PreferredDescription / Weight (of each Lego piece so we can calculate bulk weight)

    Still need a table with Bin Location but deciding where to include that as I only want information entered into one field to link them all together.

    Further Complications: Production team wants to be able to search a Lego part and then be informed of every kit this part has ever shown up in.

    So one kit could be:
    Name
    Part 30137 blue 15ct
    Part 30236 red 45ct
    Part 30241b purple 16ct

    etc, and the number of parts included in a kit can vary.

    Can anyone help me with the next step towards this goal?

    Much appreciated, I expect this to be an arduous process and I'm not the type to give up easily.

    Database I have so far (no viruses, I'm looking for honest coaching) is located at: https://drive.google.com/open?id=1qQ...wQNKsDwT05KNWC

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,603
    and youll need tKit table, filled from tMasterParts table and tColors table:
    KitID, (numeric, either co issued, or autonum)
    KitName
    Part#
    Qty
    ColorCode

    and a tLocation table:
    LocID (auto)
    Rack#
    Bin#

    and maybe tStorage table to find the parts:
    LocID
    KitID

  3. #3
    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
    14,016
    ROTL3,

    Here is a link with lots of material related to Database Planning and Design. I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in the link. You have to work through them to experience the design process and the related concepts. Each tutorial will take about 45 minutes. The other materials are great for reference and/or refresher.
    When it comes to Access, do not use field or object names with embedded spaces or special characters. Use the underscore (_)if/when needed.

    Some facts from your post:
    You have many Parts
    Parts have a specific color
    Bins contain Parts (of the same color)
    Each Bin is numbered
    Kits contain 1 to many Parts
    ...

    Not in the post:
    Do you include documentation with a kit?
    What about Customers, Orders, Sales...?

    Take your time to map out your business facts; including your required outputs; design a model (tables and relationships) on paper; create test scenarios and test data; test your model; revise until the model supports your business (and you can report/query all of your requirements); then create a physical database....... You will also have to train users on the database and related business practices; and convert from spreadsheet to database...

    Get users involved in the design process so they become part of the team. See the "stump the model" in the link for ideas.


    Good luck with your project.

  4. #4
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    131
    Orange and Ranman give great advice, nothing I say should over rule them.
    (or Steve, Arvil, Ajax, June, Micron no particular order)

    Here is what I do in understanding my data tables and fields

    1. In a new workbook and create a sheet call tables
    2. For each proposed table/field, on the top row, place the names & separation columns into a understandable groupings.
    3. Populated the rows with some common and the strangest data you will be using.
    4. Look at/for:

    Length 1 piece needs to be 300 characters long vs the normal 16
    Field types fit a number field is always numbers and not I, O or A)
    Date is really a date missing day, impossibly value, 13 month etc.
    Nulls missing data common?, special cases?, place holder (needed?) (see below)
    Special things used Graphics, symbols, etc. how to handle
    Duplication Patterns two tables/fields/records have the <almost> same data or pattern
    Near or repeating bits Blue, Lt Blue, Lt. Blue, Sky Blue, Bloe > different items or just labels?
    Comas, semi colons, colons They are signs of a enclosed list (another table)
    Generic descriptors Another table? Standardize? Free form allowed?
    Relationships 1xM, MxM, always, sort of, 1x1, occasional ... define them
    Security of Data Who can see this (1x1 relationship is good here)

    1. Remember an auto number (PK) is 4 bytes which 4 letters. So anything over 8 bytes could be moved to a different table. Space is cheap, but finding and correcting a typing error can get costly, changing, fixing assumptions in code/structure even more so.
    2. Using vlookup or index(match) create a reportable data record which test the PK/FK relationship. (Kit D, List item 9; 6 Blue 2x3 Block(s)). Try almost every report you wish to create (by state when that’s part of address hmmm)
    3. Work though the problem again adding data, checking Normalization, field and table names, and other general DB rules (table describes an single object/concept.
    4. Repeat, trying to stump the model.
    5. Create the tables & relationships in Access, correct and repeat if necessary.


    Excel is great for this because of its flexibility. You can use it sorting, checking, cleaning, changing your test data all which are good to do. This flexibility also makes it very poor for a long term solution. If you can do it Excel once, you can do it Access always.

    Step 3 is important, using common data should be easy, the hard bit, is the specials that every organization has in the closet (You know the one the boss made up to solve that 1 problem). Going forward you will need some method of handling that info, even if it’s manual, using fake data and/or “special” records. It’s better to build that in (if you can) or have some idea of how to vs “that will never happen”.

    Remember you are playing with the concepts, and structure of the DB, not the data. If want to use some fake data (Twins are employees) do so. Ask questions about your data assumptions and sources. Never assume and if you do document it (remember that 1 special case?).

    Nulls have many meanings that are not always clear:

    1. No data
    2. No meaning
    3. Data was not available at the time
    4. Zero
    5. Ditto, Normal, not special

    They also can be challenging to work with, use care with them. Your style is not everybody’s, people LOVE to bend/change your rules, so enforce data integrity.

    Ramblings of a old man
    Thanks for reading

  5. #5
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    105
    Hi

    I agree with Ranman

    You need tables as follows:-

    tblKits
    -KitId - PK - Autonumber
    -Kitname
    -PartID - Number - FK - Linked to PK from tblParts
    -Qty
    -ColorID - Number - FK - Linked to Pk from tblColors

    jxtKitLocations
    -KitLocationID - PK - Autonumber
    -KitID - FK - Linked to tblKits
    -LocationID - FK - Linked to tblLocations

    tblLocations
    -LocationID - PK - Autonumber
    -Location

    tblColors
    -ColorID - PK - Autonumber
    -ColorCode
    -Color

    tblParts
    -PartID - PK - Autonumber
    -OldPartNo
    -PartNo
    -LDrawDescription
    -PreferreedDescription
    -Weight

  6. #6
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    105
    Hi Julie

    Do you want to be able to select a Kit and see all of the part numbers associated with that specific Kit?

  7. #7
    Join Date
    Apr 2019
    Location
    Minneapolis MN
    Posts
    5
    I am working on conforming the table suggestions above - using those that make sense in my head. I'll have a new db sample of what I've worked up soon. I appreciate everyone's input!

    I've also thought of new ways this db could be important to us. So now I want the cost of each kit, plus what we sell it for. As the accountant I want to know what our COGS are at any given point in time. Cost of each kit is important for year end inventory.

    We also have 4 store locations and are growing. Knowing how many kits are where could be useful - for now our BigCommerce website is tracking inventory by location but that doesn't necessarily mean we'll use that website service forever. Could be helpful to have our own handle on that - but this is step #457 and i'm on step... 3. So just something to keep in mind during build out.

  8. #8
    Join Date
    Apr 2019
    Location
    Minneapolis MN
    Posts
    5
    New copy - edited tables and values (not that the actual values matter yet) and made relationships due to the suggestions here. Willing to check my work? I'm not the greatest with understanding relationships. We do have SKU numbers for our kits, so I matched that to KitID in tblKits instead. They're alpha numeric, so can't auto number. Looking for corrections/next steps. Thank you!

    https://drive.google.com/open?id=1EL...ilvjJHW5hpQNjy

  9. #9
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    105
    Hi
    From your Relationship window can we assume that 1 Location (Bin Nr) has 1 or More Kits?

    With Each Kit made up of Many Parts?

    I would recommend that you Enforce Referential Integrity between all tables in order that you do not create Orphaned Records.

    I would also recommend that you change the name of KitSKU in jxtKitLocation to KitID as it is linked to KitID in tblKits

  10. #10
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,317
    If KitSku is unique then make it a Unique index, that will prevent duplications.

    What purpose does KitLocation in the junction table serve over the location ID ?

    I would have a Replacement Part Number field instead of an Old part number field.
    It allows you to Automatically substitute the new part number automatically rather than having to look up if the part number entered is anywhere in the list as an Old Part number.

    Are you going to be keeping track of how many complete kits you have made / are in stock?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    131
    1. Corrections:
      1. Tables should be plural has they have many things in them so:
        1. tblLocation should be tblLocations.
        2. jxtKitLocation should be jxtKitLocations or jxtKitsLocations.

      2. KitSku is not set up as the PK, KitID is. Using an autonumber is better in many ways. The SKU is a descriptor of the Kit.
      3. KitLocation is not needed in the junction table. DB are messy to read, but LocationID contains that info too.

    2. Comments:
      1. You have nulls in Weights which will throw off things likes counts unless handled somehow (I’m currently on a null hate). Nulls in descriptions give nothing away.
      2. Double spaces in PreferredDescription will require a trim statement on output. Is that what you want?
      3. Need a Junction table between Parts and Kits
        1. A Part may be in many Kits, A Kit many have many Parts (MxM).
        2. So, ID, Kit, Part, Qty, <Color>
        3. If Color doesn’t matter, then it should in this junction table too.

      4. Does Color matter to a part?
        1. Are Parts sorted/Stored by color or all mixed together?
          1. If Color matters to the “Bit” then there is a junction table between Colors and Parts: A Part may be in many Colors, A Color many be used in many Parts (MxM).

        2. I think you have two (or three) entries mix together in Parts
          1. “Specs” which is “Brick 2 x 2”
          2. “Bits” which are “Brick 2 x 2, Brown” (Physical)
          3. Tools (I thought a saw one or two)

      5. Will Kits, Parts and Colors need to be sort in any other way besides the fields already declared? (by tint “Chrome Blue”, by base “Blue, Chrome”)
      6. You may need a junction table between parts and Locations or is location only for kits?

    3. Next Steps:
      1. To get a count on the number of kits in a location, you will need an Order system for adding and subtracting kits.
      2. Fill in the tables. Kits, the key one (now), is empty and gives you no clues on its normalization.
      3. Do you have all the fields need to create the outputs required? Remember that you want to calculate things, not store results (unless you need to). eg. Kit create on, revised on, Last made on


    Ramblings of a Old man
    Thanks for reading

  12. #12
    Join Date
    Apr 2017
    Posts
    923
    Attached is extremely simplified example (table structures, plus some comments and example data), which allows to keep track of used and present quantities od various parts color-wise, and sold and stored quantities of filled kits.

    To get current saldo of certain part in certain storage you have to add all incomings and subtract all outgoings of this part in this storage.
    Attached Files Attached Files

  13. #13
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    105
    Hi

    Your example Inventory Example database does not really help the discussion as you have not set any Referential Integrity between tables. Is the Op supposed to guess these?

  14. #14
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,317
    Referential integrity really isn't necessary at the design stage, it's a tool to prevent incorrect data entry or deletions as an end user protection imho.

    In fact when your designing and building the core elements it can be a right royal pita.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #15
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    105
    Hi Minty

    I would tend to disagree as the OP is a complete beginner and should follow good design parameters.

    This article will give the OP some guidance regarding Referencial Integrity:- http://www.dbta.com/Columns/DBA-Corn...ty-119799.aspx

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-11-2015, 08:13 PM
  2. Replies: 1
    Last Post: 11-05-2014, 11:08 AM
  3. Replies: 1
    Last Post: 02-21-2013, 12:18 AM
  4. Products Inventory Dilema,Add To Inventory
    By burrina in forum Forms
    Replies: 3
    Last Post: 12-02-2012, 12:10 PM
  5. Invoice for kits
    By tomasreabe in forum Access
    Replies: 1
    Last Post: 09-22-2011, 04:33 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
  •  
Tech Forums: Microsoft Office Forums