I am a complete Access beginner trying to set up a parts price database for a small manufacturing business. We only check stock levels once a year.
I want to be able to enter a vendor, a part number, a date, and our cost, and then be able to look up what our sell price was for a particular item on a particular date, or what our current sell price should be. I want to enter quantities once a year and look up the total value of everything we have for that year's taxes.
When I order a part, I order it from whatever vendor has it for the lowest price, so one part might have 3 part numbers.
For some items I might order 20 pounds of a material, enter our cost for 100 pounds, and then look up our sell price for one square foot.
For other items I order a box of 50 and then look up the sell price for one.
Do I need a table of every part I have, and another for each vendor that lists every part they sell us? Do I need a table that is just every part and all possible part numbers for that part?
Do I need to separate items by how we buy them? (By weight, by length, by number?) What about if some vendors sell the same part by weight, and others sell it by piece?
What about when the same vendor uses two different part numbers for the same material depending on the amount bought? (The part number for a 5X10 sheet is different than a 4X8, but the sheet is sold per square foot, so the cost per foot is the same)