 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / January 2008How do I setup a products database that has different values?
I'm developing Access management database for a Cushion manufacturing company. I've only learnt basics of Access so i decided to post a question on the community. Problem is that there are at least 2 variations when we manufacture a cushion as the customer's requests are different all the time. For example, we have a cushion "cover" called #5778 which we keep 500 of them as a stock. When we receive an order from a customer, we fill them up with fibre according to the customers weight request and charge them accordingly. For example, Customer A may want 100 pcs of #5778 with the filling 500g and we charge them $2.00 Customer B may want 50pcs of the same #5778 with the filling 750g and we charge them $3.00 The problem is that if i create a two separate product list as above, i'd end up with two different raw material as stock (#5778 cover), which doesn't reflect the correct stock left. And if I create one product called #5778, then i'll have to enter the weight and the price everytime the customer places order. Basically, i'm trying to create a link between "Stock Record Table" and "Product Table" so that one particular "stock" can be created into various products at different weight and price. Please post me some advice asap! Hi Mike, creating a database that manages stock and products from scratch with only basic Access knowledge is a huge undertaking. If you were creating a database to manage your movie collection or contacts, it would be much easier for you. I suggest that you get a copy of the Northwind database and see if you can change it to suit your cushion business. We could help you with that. Jeanette Cunningham > I'm developing Access management database for a Cushion manufacturing > company. [quoted text clipped - 31 lines] > > Please post me some advice asap! Maybe this is a logic answer and not an Access answer, but seems like you need a different number for each type of finished product, each of which uses your standard #5778 blank. Say 5778-500 and 5778-750, your first 4 digits representing the id of the cushion blank and the remaing 3 digits representing the amount of fill. You track the cushion blank by the first part and the quantity of fill with the latter part of your id number. > Problem is that there are at least 2 variations when we manufacture a > cushion as the customer's requests are different all the time. > And if I create one product called #5778, then i'll have to enter the weight > and the price everytime the customer places order. Some Ideas - maybe not worth much since I don't know the rest of your business. Customer Table ID auto number Customer Name Address etc. Customer Order Table ID auto number CustID ID number from customer table Order Number optional or what ever Order Date etc Cushion Model Table ID autonumber Model Number (ex 5778) Model Desc Model base Charge $ just for this with no filling (maybe future use) Model etc whatever else is unique to cussion Cushion Fill Table ID autonumber Fill Description Fill Quantity - standard unit always lbs or oz or also need a field for Fill Unit and this field will be the number. Fill Charge $$ Customer Order Item Table ID autonumber CustOrderID ID number from Customer Order Table CushionID ID number of cushion ordered CustionBase $ loaded when you select cushion (this is so that this price will remain the same 6 months from now when the actual price has gone up and you need this item to show the price at the time ordered. FillID id number of Fill Table entry Fill Quantity (and units if used) Fill Charge $$ (Both of these have the same comment as CustomerBase $ above) When filling out the customer order item record use dropdowns to capture and load the information for each Cushion ID and FillID. Also look at the Northwind database to help in getting some more ideas on this. Ron > Basically, i'm trying to create a link between "Stock Record Table" and > "Product Table" so that one particular "stock" can be created into various > products at different weight and price. StockTable: (raw ingredients) StockID StockDescription 5778 Cushion Cover 9999 FibreFill ProductStock table (aka Recipes: translate raw ingredients to finished product) ProductID StockQty StockID 5778-A 1 5778 5778-A 500g 9999 5778-B 1 5778 5778-B 750g 9999 ProductTable (finished product) ProductID ProductDescription Price 5778-A Cushion w/500g Fill $2.00 5778-B Cushion w/750g Fill $3.00 Sometimes (perhaps usually) the ProductStock table will only have one entry for a finished product. That's fine. While it might seem to be redundant, its necessary to handle the products that require multiple ingredients (per your example) and gives you a lot of flexibility of in creating new products or product packages.  SignatureHTH, George > I'm developing Access management database for a Cushion manufacturing > company. [quoted text clipped - 31 lines] > > Please post me some advice asap! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.