Data Structure

[Daryl J. Edwards]

I'm trying to set up a database that will do the following for me:

1) store part numbers, descriptions, and costs in one table (the easy
part)
2) store "kits" - kits are groups of parts from the above table (ie 1
of part A, 0 of part B, and 3 of part C) that are used for certain
construction jobs.
3) for some jobs, multiple kits will need to be used, so i need to
show the total cost of a job that uses eg 2 of kit A, 1 of kit B, and
3 of kit C.
4) i also need to generate a list of part # and quantity of that part
used for eg the job above.

Anybody have any ideas for how i should set up my data structure? I
thought i might use one table for the parts list (part #, description,
cost), a table for info about the kits, and a third table to connect
the first 2 together. I'm not sure how i would actually do this... i'm
having a lot of trouble getting my heard around this.

another thought i had was to make a table for the parts list, then a
new table for each kit, and have each link back to the master parts
list. if i did it this way, how would i do the calculations in #3 and
4 of my above list?

Thanks in advance! Any help at all would be greatly appreciated.

[PC Datasheet]

TblParts
PartsID
PartNum
Desc
Cost

TblPartKit
PartKitID
PartsID
PartQuantity

TblJob
JobID
<Job Fields>

TblKitForJob
KitForJobID
JobID
PartKitID
PartKitQuantity

Your questions 3 and 4 require a query on the above tables. I suggest you
try to create the query for 4 and include the part cost. Then on your
form/report, use a calculated field to get the answer to question 3. The
calculated field is done by adding an unbound textbox to your form/report
and putting the following expression in the control source:
= Sum([Cost])

I am in business to provide customers with a resource for help with Access.
If you need outside help, please contact me at my email address below.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Daryl J. Edwards" <da***********@utoronto.ca> wrote in message
news:de**************************@posting.google.c om...

I'm trying to set up a database that will do the following for me:

1) store part numbers, descriptions, and costs in one table (the easy
part)
2) store "kits" - kits are groups of parts from the above table (ie 1
of part A, 0 of part B, and 3 of part C) that are used for certain
construction jobs.
3) for some jobs, multiple kits will need to be used, so i need to
show the total cost of a job that uses eg 2 of kit A, 1 of kit B, and
3 of kit C.
4) i also need to generate a list of part # and quantity of that part
used for eg the job above.

Anybody have any ideas for how i should set up my data structure? I
thought i might use one table for the parts list (part #, description,
cost), a table for info about the kits, and a third table to connect
the first 2 together. I'm not sure how i would actually do this... i'm
having a lot of trouble getting my heard around this.

another thought i had was to make a table for the parts list, then a
new table for each kit, and have each link back to the master parts
list. if i did it this way, how would i do the calculations in #3 and
4 of my above list?

Thanks in advance! Any help at all would be greatly appreciated.

[Alan Webb]

Daryl,
Search online for software & databases that do multiple bills of materials.
Basically, it's many-to-many between items (parts) and jobs. As with a
classic bill of materials, the highest level is a finished good (job) then
there is a hierarchy of things made of things until you get down to
consumable components like screws, shrinkwrap, etc. So, there is an item
master table that describes all the things needed for a kit used in a job.
There is a bill of materials table that lists the components of a kit. Then
there is a kind of line-item table that lists the kits used in a job that is
functionally similar to the line item table you would make to do invoices.
So, similar to PC Datasheet's design:
ItemMaster
RowID
PartNo
PartName
PartDesc.
FinishedGoodUnitCost (for finished goods where the business chooses a price
other than the sum of the price of the components)
FinishedGood (true or false, true if this is a finished good. You would not
expect a finished good to be part of something else)
FinishedGoodUOM (finished good unit of measure if different from units of
measure of components), etc.

BillOfMaterials (Kit Components)
RowID
ChildPart (row id number from Item Master of child part)
ParentPart (row id number from Item Master of parent part)
and any additional columns needed to track information related to this
parent/child part pairing.

Job
RowID
JobName
Client
etc.

KitJob
BOM_RowID (Kit ID)
JobID
and anything else about the kit/job that the business needs.

"Daryl J. Edwards" <da***********@utoronto.ca> wrote in message
news:de**************************@posting.google.c om...

I'm trying to set up a database that will do the following for me:

1) store part numbers, descriptions, and costs in one table (the easy
part)
2) store "kits" - kits are groups of parts from the above table (ie 1
of part A, 0 of part B, and 3 of part C) that are used for certain
construction jobs.
3) for some jobs, multiple kits will need to be used, so i need to
show the total cost of a job that uses eg 2 of kit A, 1 of kit B, and
3 of kit C.
4) i also need to generate a list of part # and quantity of that part
used for eg the job above.

Anybody have any ideas for how i should set up my data structure? I
thought i might use one table for the parts list (part #, description,
cost), a table for info about the kits, and a third table to connect
the first 2 together. I'm not sure how i would actually do this... i'm
having a lot of trouble getting my heard around this.

another thought i had was to make a table for the parts list, then a
new table for each kit, and have each link back to the master parts
list. if i did it this way, how would i do the calculations in #3 and
4 of my above list?

Thanks in advance! Any help at all would be greatly appreciated.

[Pieter Linden]

da***********@utoronto.ca (Daryl J. Edwards) wrote in message news:<de**************************@posting.google. com>...

I'm trying to set up a database that will do the following for me:

1) store part numbers, descriptions, and costs in one table (the easy
part)
2) store "kits" - kits are groups of parts from the above table (ie 1
of part A, 0 of part B, and 3 of part C) that are used for certain
construction jobs.
3) for some jobs, multiple kits will need to be used, so i need to
show the total cost of a job that uses eg 2 of kit A, 1 of kit B, and
3 of kit C.
4) i also need to generate a list of part # and quantity of that part
used for eg the job above.

Job---(1,M)---Kit---(1,M)---KitContents----(M,1)---Part(partNo,Price,...)

from this you can just create a totals query. Job-KitContents(sum(PartNo))