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