How do I setup a products database that has different values?

[Mike]

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!

 

[Jeanette Cunningham]

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

 

[JimH]

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.

 

[Ron2006]

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

 

[George Nicholson]

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.