help with a manufacture database

[Simon]

I will try and explain what i want to do.
I am building a datbase for manufactuing hot tubs


i have the following tables
tblHotTubs Containers - HotTubID, and ProductName and price
tblProducts Contains the products need to build tub - ProductID,
Product name, Price
tblParts Contains all the parts for each hot tubs -
HotTubID , ProductID, Quantity
tblManufacture Contins the date it was made and HotTubID -
ManufactuerID, Date, HotTubID
tblPartsUsed Contain all the parts used in builidn hot tub -
ManufactureID, ProductID, Quantity, Serials Number


I have a have a form (frmManufactuerTub) where i have a autonumber for
maufactuer ID, i also have a datasheet subform frmPArtsUsed)

what i would like to do is on the main form when i select the hot tub
code what is made it puts all the parts for that hot tub which are
found in tblParts into the subform frmPArtsUsed (tblPArtsUsed)

then once all the intem are in the suform i can add the serial number
of any of the products and can all increase and decreas the products
uses if the hot tub needs to be speceted up or down.


Hope this is clean, if you need any other info just ask, i am just so
struck on how to go about this


Regrads

Simon

 

[JK]

Simon,

If I understand you correctly, you manufacture hot tubs out of components
needed to manufacture a particular tub.

What is the difference between tblProducts which "Contains the products need
to build tub" and tblParts which "Contains all the parts for each hot
tubs"?

It seems to me that, unless I miss something, one of theses two tables is
redundant. Please comment.


Regards/JK

 

[Simon]

Hi

The differance bettwen the two tables is that

tblProducts contains all the items we buy in and hold infomation like
suppliers and costs to purchase.

tblParts
This holds the Hot tub poroduct coded along with the Productd code and
quantity need in the manufacture of the tub

Eg. of what is held in each table
tblProducts
ProductCode JETL
ProductName Large Jet
SupplierID 1
Cost £2.00

tblProduct
HotTubsCode TUB1
Product Code JETL
Quantity 10



hope you can help as im stuck on how to buil it

 

[JK]

Thanks Simon,

I see what you are trying to do, but I still have problem with DB structure
(Normalisation).


tblHotTubs Containers - HotTubID, and ProductName and price
*** OK as you have it ***

tblProducts Contains the products need to build tub - ProductID,
Product name, Price
*** OK but:
Add "OpeningStock" (? and "CurrentStock") fields, this will obviate the need
for "tblPartUsed" because you can find out the stock (inventory) on hand
using a query.
*******

tblParts Contains all the parts for each hot tubs -
HotTubID , ProductID, Quantity
***OK as You have it (Both "HotTubID" *and* "ProductID" should be Keys to
avoid duplications)

tblManufacture Contins the date it was made and HotTubID -
ManufactuerID, Date, HotTubID
*** you have "HotTubID twice here, do you mean "SerialNumber? ***

tblPartsUsed Contain all the parts used in builidn hot tub -
ManufactureID, ProductID, Quantity, Serials Number
*** In my opinion not needed, *unless* you want to track serial number of
*each parts* (product). Is this the case???
********

Comments:

The I understand it, you wish to track the stock on hand of tubs and
components (your "Products") used to produce tubs. That being the case, I
assume that you have other tables to deal with it, i.e. tblPurchases" (or
such like) to add "product" into inventory when you buy parts and
"tblInvoice" (or such like) when you sell tubs.

Since you mentioned "Serial Number", the codes required to update your
tables depend on whether you track serial numbers of components (your
Products), tubs or both.

(Sorry for late reply)

Regards
Jacob


Hi

The differance bettwen the two tables is that

tblProducts contains all the items we buy in and hold infomation like
suppliers and costs to purchase.

tblParts
This holds the Hot tub poroduct coded along with the Productd code and
quantity need in the manufacture of the tub

Eg. of what is held in each table
tblProducts
ProductCode JETL
ProductName Large Jet
SupplierID 1
Cost 2.00

tblProduct
HotTubsCode TUB1
Product Code JETL
Quantity 10



hope you can help as im stuck on how to buil it