Hey Rod, much like "Leander" I am reading this post in 2014 also. I also am having a terrible time creating a somewhat (or at least thought to be) simple inventory system.
I have laid the structure out on paper, listed everything in Excel to see what it may look like, and created about 7 inoperable databases on my desktop (I guess to remind me of how stupid I can be!).
Anyway, this is the setup:
- We are a University located in Washington, DC
- We have about 100 vehicles/pieces of equipment in our fleet
- (just for your knowledge) they include everything from buses to lawn mowers
- We already have a database (that I created) that tracks:
- each vehicle's basic information (year, VIN, license plate, truck number, etc.)
- each vehicle's drive train information (engine size, trans type, ABS system, etc.)
- each vehicle's "Service Details" information (type & qty of oil, size of tires, size of windshield wipers, oil, air & fuel filter numbers, etc.)
- each vehicle's "Service History" (what has been done & when, etc.)
- additional notes for each vehicle
Now, up to this point, all of the parts that we may have "stocked" for these vehicles was collected in a "pile" in a fairly small room. It basically consisted of a pile of parts that, when we ordered, probably ordered two of while only using one of them. The second apparently went into this "pile".
One Saturday we came in and cleaned out the "pile/room". We were not surprised to see that most of the parts in there belonged to vehicles/equipment that we had long since gotten rid of.
To avoid EVER running into this situation again, we have recently built a moderate sized store room to store these parts. We have also sought the assistance of one of our parts suppliers to help us lay the area out for best use.
This is what we came up with:
- As stated above, each vehicle/piece of equipment has an assigned number to it (known as the CUA#)
- Our storage room has several large racks in it
- Each rack is broken down by:
- Rack Type (Vehicle, Mower, Snow Equipment, and otehrs to be named in a future date (we're still working on those shelves - lol)
- Shelf Number (self explanatory)
- Bin Number (counted from left to right)
- Each Bin is assigned a Vehicle/Mower/etc. number
- Each Bin contains a certain assortment of "Stored Parts" for that vehicle/piece of equipment
An example would be:
- "Vehicles" Rack, "#2" Shelf, "#4" Bin belongs to "CUA# 103", and contains:
- Air Filter, Oil Filter, Wiper Blades, Front Brake Pads, Rear Brake Pads
Here is what I am trying to do:
Inventory the parts that I currently have "In Stock" in the bins and put together an order for the parts needed to fill the bins properly.
So, in other words, if we were to do a service on CUA# 103 today, we would probably use the "Air Filter" (PN AF484), "Oil Filter" (PN LF110), and maybe the "Front Brake Pads" (PN 106.11940). Now, since the "Air Filter", "Oil Filter", and "Wiper Blades" come from one Vendor, while the "Front & Rear Brake Pads" come from another, at the end of the week when I did my inventory, my "Order to be Placed" might look like this:
Parts to Order:Vendor: Parts Authority
Part Number: AF484 Qty.: 1
Part Number: LF110 Qty.: 1
Vendor: DelcolinePart Number: 106.11940 Qty.: 1
My problem is here:
I AM CONFUSING THE HECK OUT OF MYSELF DESIGNING, RE-DESIGNING, AND OVER RE-DESIGNING THIS STUPID THING!!!
Here's what I have thus far:
A "Brands" table:
A "Categories" table:
A "Parts" table:
A "Suppliers" table:
...and a "Vehicles" table:
How in the world I built a massive database like our vehicles one and cannot figure this one out is beyond me!!! (Why does Access always make me feel like an idiot?!?)
Anyway, if you have any time that you could use to put me on the right track here, I would GREATLY appreciate it!!!
Thank you in advance for your time and consideration in this matter,
DG