Filtering Parts Orders

[Rick]

I have a customer order form with a subform that lists parts per
customer order that I'm updating. I'm trying to filer the parts list
per Manufacture and Model number so that the user doesn't have to
scroll through the complete list of parts to find the parts that relate
to the model in question. I've got two combo boxes on the main form
where you can select the manufacture, that filters the model combo box
to show only models from that maker, that in turn filters the combo box
in the subform to list only parts for that model.

I've got all that working well but I'm trying to decide how best to
proceed with one final step. Some of the models have parts in common
with other models. I have a table that lists manufactures which is
related to a model table by ManfID. My products table has colum for
Manufacture and colum for model number. I would like to find a way to
relate models with common parts so I don't have to enter the same part
number for each different model. In other words I don't want to have 5
of the same parts listed in the products table to relate to each
different model.

I know I probably don't need the manufacture listed in the products
table but I use that for updating pricing from different manufactures.
It would seem that I need one more table field somewhere to tie common
parts between models together but my brain seems to have locked up
trying to figure it out. I've tried rebooting it a couple times but
it's Monday here and It's slow starting up. Any help would be
appreciated

[rkc]

Rick wrote:

I have a customer order form with a subform that lists parts per
customer order that I'm updating. I'm trying to filer the parts list
per Manufacture and Model number so that the user doesn't have to
scroll through the complete list of parts to find the parts that relate
to the model in question. I've got two combo boxes on the main form
where you can select the manufacture, that filters the model combo box
to show only models from that maker, that in turn filters the combo box
in the subform to list only parts for that model.

I've got all that working well but I'm trying to decide how best to
proceed with one final step. Some of the models have parts in common
with other models. I have a table that lists manufactures which is
related to a model table by ManfID. My products table has colum for
Manufacture and colum for model number. I would like to find a way to
relate models with common parts so I don't have to enter the same part
number for each different model. In other words I don't want to have 5
of the same parts listed in the products table to relate to each
different model.

I know I probably don't need the manufacture listed in the products
table but I use that for updating pricing from different manufactures.
It would seem that I need one more table field somewhere to tie common
parts between models together but my brain seems to have locked up
trying to figure it out. I've tried rebooting it a couple times but
it's Monday here and It's slow starting up. Any help would be
appreciated

It would be nice to see the table structure you're working with,
but the short answer is that you have a many to many relationship
between Models and Parts. A many to many relationship is modeled
using a junction table that references the primary keys from the
two participating tables.

ModelsParts (ModelID*, PartID*)

It sounds like you also have a many to many relationship between
Parts And Manufacturers. Same theory applies to that relationship.
ManufacturersPa rts(Manufacture rID*, PartID*, PartPrice)

[Rick]

rkc wrote:

It would be nice to see the table structure you're working with,
but the short answer is that you have a many to many relationship
between Models and Parts. A many to many relationship is modeled
using a junction table that references the primary keys from the
two participating tables.

ModelsParts (ModelID*, PartID*)

It sounds like you also have a many to many relationship between
Parts And Manufacturers. Same theory applies to that relationship.
ManufacturersPa rts(Manufacture rID*, PartID*, PartPrice)

Here it is;
tblProducts
ProductID
ProductName
PartNumber
UnitPrice
ManfID
ModelNum

tblManf
ManfID
ManfName

tblModel
ManfID
Model

[Rick]

For those interested I solved this by adding one more field to Products
table and one field to Model table. I added AssocPart to each table.
Parts that are associated to many models have no model, just a type of
AssocPart. I then added AssocPart column to my filter select combo
box, and added an OR statement to the parts order SQL to select all
parts with selected ModelNum OR AssocPart.