I am building an Access database for a small company that builds patio covers and then ships them out to customers.
I am running into a structural problem. Take this small example:
Each patio cover consists of a set of louvers. Depending on the specifications given to our company by the customers, each patio cover may have a different sized louvers. For instance, one order may call for a patio cover with 40 louvers that are 18ft in length. Another order could call for 52 louvers at 20ft in length.
These louvers are ordered by our company in standard 150ft sizes, which must be cut to meet customer requests.
Now, I am looking to set up this database in the most efficient manner that would be able to calculate INVENTORY. This needs to include inventory of any scrap pieces cut from the original 150ft louver (so that they can be used again on another job).
Structurally speaking, below is what I have so far in terms of tables. The louvers will be stored in the item table. Where should I store the louver sizes? I am thinking I shouldn't put them in the item table because then there would have to be 150+ entries for louvers alone.
Assuming that an "Item" as you defined it, is a batch of purchased qty of a specific product, and there is some kind of "cutting" operation that changes one of the properties (i.e. the length), I would created an inventory table ItemID, ItemLength, qty.
Then this "cutting" transaction would allocate qty's of items to orders and generate "scrap" records with different lengths.
For example assuming you have an order of 40 louvers 18ft lentgh, and that you start from a "clean" inventory of 10 items of 150ft, you would allocate 5 louvers to that order and remain with 5 louvers of 6 ft (I would assume there are more intelligent ways of solving this).
Am I getting close at all?