Table structure to handle Bill of Materials

[CharmCityRob]

I am working on a project in which we receive and track Bill of Materials for a network project we are doing. The bill of material is layed out as so:

Header
Bill of Material number
Bill of Material date
site id where it will be installed
engineer responsbile for installation


Details
product numbers, description, qty and cost of each item that will be deployed
** There may be multiple details sections based on the number of wiring closets at the site

Here is how the tables are laid out currently, names are not exactly as they are in the DB but you get the idea.


SITE_INFORMATION
siteID
sAddr1
sAddr2
sCity
sState
sZip


BOM_HEADER
bomID
siteID
Date
Engineer

BOM_CLOSET
closetID (autonumber)
bomID
closetDescription

BOM_DETAILS
closetID
PartNo
qty
ListPrice
Cost
ExtCost


The relationship is between SITE_INFORMATION and BOM_HEADER on siteID then further links from the BOM_HEADER to BOM_CLOSET and BOM_DETAILS tables. I have a form created that is using subforms to present all the information together on one screen so the information can be input and all seems to be working ok. I am just wondering if there is a better way to go about this.

I would like to create a dashboard form for the database that would include a list of all Bill of Materials and the total for it but I am not able to Sum the extCost field in a query. I am able to get the siteID for all sites that have a Bill of Material currently but cant get the second field to be the total of all items by that siteid. Any ideas?

Thanks for any assistance you may be able to provide me.

 

[jedraw]

see here for an example

http://www.databaseanswers.org/data_models/bom/physical_bom.htm