stuck on a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I'm trying to accomplish:

I have a table of products, which a user can select from in a form to create
an order (each product has a check box for the user to check if they want
it). Some products depend on eachother, (i.e. If product A is chosen, C must
also be chosen) so I need a way to have product C automatically selected as
soon as A is selected, to prevent the user from forgetting to purchase
necessary components.

How I'm doing it thus far:
Right now I have a cross reference table, which have a list of products in
one column, and the products they depend on in another (i.e. A in one, C
right next to it). I have a query running that brings back the products that
were selected by the user AND on the the dependency table (i.e. product A) so
they have further components that must be selected. However, I am stuck
here----I need to figure out how I can then set the option of JUST the
"further necessary components" to TRUE, which in some way means relating the
option field of the Dependency table to the original master products table,
but I haven't been able to make this work yet.

Any ideas?
 
One product can have many dependent products (products that must be
purchased if you choose the first one), and a product can also be dependent
on many products. This implies a many-to-many relation between products, so
you need a junction table between 2 copies of the Product table.

This ProductDependency table will have fields:
- MasterProductID the product that has dependencies.
- ChildProductID the product you must buy when you buy the
MasterProductID.

To create the relationships, drag a 2nd copy of the Product table into the
Relationships window. Access aliases it as Product_1. Now create two
relationships:
- Product.ProductID to ProductDependency.MasterProductID
- Product_1.ProductID to ProductDependency.ChildProductID

That takes care of the structure. Now for for interface.

In the AfterInsert event procedure of the form where the user adds a new
product, execute an Append query statement that also add the dependent
products. This is the basic "trigger" you are trying to simulate, I think,
just to ensure the user does not forget them.

There could be a range of other issues to consider, such as:
a) Quantity of related product. If someone buys 1 widget or 10 widgets, they
may only need 1 glue pot.

b) User editing a record to choose a different product. Do you need to
remove the dependent products too?

c) User editing the quantity of a product. Do you need to edit the
quantities of dependent products too?

d) User deleting a product. Do you need to delete dependent products too?

e) Further dependencies. Example if the user buys product A which requires
product B so you automatically add that. But product B requires product C,
so do you automatically add that as well? And what if C requires more? Where
does it stop? Worse, does it go in circles, e.g. if C requires A?
 
Back
Top