Many to Many Table design

[Gary]

I am having trouble with a many to many issue. We are a Yacht charter
business and each charter order can have many products associated. Each
product may have one or many components. I have created tables as follows:

tblProducts
ProductId PK
ProductDescrip
ProductCost
ProductCategoryId

tblComponents
ComponentId Pk
ComponentDescription
SupplierId
ComponentCost
ProductCategoryId

tblProductComponents join table
ProductId CPk
ComponentId CPk
ComponentQuant

I need to be able to build a product record from one form. I have created a
main form (frmProducts) based on tblProducts and a subform
(sfrProductComponents) based on tblProductComponents. I need to sum all
components and pass that amount to ProductCost on main form. I do not
understand how to get the ComponentCost information to the subform
(sfrProductComponents).

I would appreciate any assistance for this phase of our project.

 

[Allen Browne]

The short answer to your question is that you should drop the ProductCost
field from tblProducts. To get the product cost:
1. Create a query using all 3 tables.

2. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.

3. Drag the fields from tblProducts into the grid.
Access Group By in the Total row under these fields.

4. In a fresh column of the Field row, enter:
ProductCost: [ComponentQuant] * [ComponentCost]
Choose Sum in the Total row.

Of course, the complete table structure could get more involved if any of
these situations apply:

a) You source some components from multiple suppliers. Requires you to build
a query to choose the preferred or cheapest supplier.

b) You need to set up a structure that allows you to handle orders for
components, not just products. In this case, the compents and products need
to be in the same table, with tblProductComponents as a junction table
between 2 copies of the Products table.

c) Some components consist of other components.
"Bill of materials" structure, discussed here:
http://www.mvps.org/access/modules/mdl0027.htm

 

[Gary]

Thank you Allen for your prompt reply....have to get ready for triip but
intend to get right on this as soon as I return

Thanks

The short answer to your question is that you should drop the ProductCost
field from tblProducts. To get the product cost:
1. Create a query using all 3 tables.

2. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.

3. Drag the fields from tblProducts into the grid.
Access Group By in the Total row under these fields.

4. In a fresh column of the Field row, enter:
ProductCost: [ComponentQuant] * [ComponentCost]
Choose Sum in the Total row.

Of course, the complete table structure could get more involved if any of
these situations apply:

a) You source some components from multiple suppliers. Requires you to build
a query to choose the preferred or cheapest supplier.

b) You need to set up a structure that allows you to handle orders for
components, not just products. In this case, the compents and products need
to be in the same table, with tblProductComponents as a junction table
between 2 copies of the Products table.

c) Some components consist of other components.
"Bill of materials" structure, discussed here:
http://www.mvps.org/access/modules/mdl0027.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am having trouble with a many to many issue. We are a Yacht charter
business and each charter order can have many products associated. Each
product may have one or many components. I have created tables as
follows:

tblProducts
ProductId PK
ProductDescrip
ProductCost
ProductCategoryId

tblComponents
ComponentId Pk
ComponentDescription
SupplierId
ComponentCost
ProductCategoryId

tblProductComponents join table
ProductId CPk
ComponentId CPk
ComponentQuant

I need to be able to build a product record from one form. I have created
a
main form (frmProducts) based on tblProducts and a subform
(sfrProductComponents) based on tblProductComponents. I need to sum all
components and pass that amount to ProductCost on main form. I do not
understand how to get the ComponentCost information to the subform
(sfrProductComponents).

I would appreciate any assistance for this phase of our project.

 

[Gary]

Allen:
Where do I put this query, because when I set the record source of the
frmProducts to the query, nothing works. If I leave the frmProducts set to
tblProducts then I get a #Name in the ProductCost text box on the Main form.

The short answer to your question is that you should drop the ProductCost
field from tblProducts. To get the product cost:
1. Create a query using all 3 tables.

2. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.

3. Drag the fields from tblProducts into the grid.
Access Group By in the Total row under these fields.

4. In a fresh column of the Field row, enter:
ProductCost: [ComponentQuant] * [ComponentCost]
Choose Sum in the Total row.

Of course, the complete table structure could get more involved if any of
these situations apply:

a) You source some components from multiple suppliers. Requires you to build
a query to choose the preferred or cheapest supplier.

b) You need to set up a structure that allows you to handle orders for
components, not just products. In this case, the compents and products need
to be in the same table, with tblProductComponents as a junction table
between 2 copies of the Products table.

c) Some components consist of other components.
"Bill of materials" structure, discussed here:
http://www.mvps.org/access/modules/mdl0027.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am having trouble with a many to many issue. We are a Yacht charter
business and each charter order can have many products associated. Each
product may have one or many components. I have created tables as
follows:

tblProducts
ProductId PK
ProductDescrip
ProductCost
ProductCategoryId

tblComponents
ComponentId Pk
ComponentDescription
SupplierId
ComponentCost
ProductCategoryId

tblProductComponents join table
ProductId CPk
ComponentId CPk
ComponentQuant

I need to be able to build a product record from one form. I have created
a
main form (frmProducts) based on tblProducts and a subform
(sfrProductComponents) based on tblProductComponents. I need to sum all
components and pass that amount to ProductCost on main form. I do not
understand how to get the ComponentCost information to the subform
(sfrProductComponents).

I would appreciate any assistance for this phase of our project.

 

[Allen Browne]

The query would be useful for when you want to see all products with costs.

To get just the cost for one product, you could then use DLookup() on that
query, or DSum() on a simpler query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen:
Where do I put this query, because when I set the record source of the
frmProducts to the query, nothing works. If I leave the frmProducts set
to
tblProducts then I get a #Name in the ProductCost text box on the Main
form.

The short answer to your question is that you should drop the ProductCost
field from tblProducts. To get the product cost:
1. Create a query using all 3 tables.

2. Depress the Total button on the toolbar (upper Sigma icon.)
Access adds a Total row to the grid.

3. Drag the fields from tblProducts into the grid.
Access Group By in the Total row under these fields.

4. In a fresh column of the Field row, enter:
ProductCost: [ComponentQuant] * [ComponentCost]
Choose Sum in the Total row.

Of course, the complete table structure could get more involved if any of
these situations apply:

a) You source some components from multiple suppliers. Requires you to
build
a query to choose the preferred or cheapest supplier.

b) You need to set up a structure that allows you to handle orders for
components, not just products. In this case, the compents and products
need
to be in the same table, with tblProductComponents as a junction table
between 2 copies of the Products table.

c) Some components consist of other components.
"Bill of materials" structure, discussed here:
http://www.mvps.org/access/modules/mdl0027.htm

I am having trouble with a many to many issue. We are a Yacht charter
business and each charter order can have many products associated.
Each
product may have one or many components. I have created tables as
follows:

tblProducts
ProductId PK
ProductDescrip
ProductCost
ProductCategoryId

tblComponents
ComponentId Pk
ComponentDescription
SupplierId
ComponentCost
ProductCategoryId

tblProductComponents join table
ProductId CPk
ComponentId CPk
ComponentQuant

I need to be able to build a product record from one form. I have
created
a
main form (frmProducts) based on tblProducts and a subform
(sfrProductComponents) based on tblProductComponents. I need to sum all
components and pass that amount to ProductCost on main form. I do not
understand how to get the ComponentCost information to the subform
(sfrProductComponents).

I would appreciate any assistance for this phase of our project.