complicated relantionship

[Gen]

Hi there,

I'm struggling trying to design an appropriate relationship for some data. I
want to create a database of batch recipes. Each batch can include one or
more ingedients, and each ingredient can have one or more supplier. Ideally,
the form would have the batch number, then a datasheet subform where the user
can select one or more ingreients using a combobox (CBO1), then select the
appropriate supplier/Lot# from a second combobox (CBO2) which is filtered by
the selection in CBO1.

My table design has four tables: tblBatch, tbIngName, tblIngSource and
tblIngDetail with the following feilds:

tblBatch
BatchID*

tblIngName
IngNameID*
IngName

tblIngSource
IngSourceID*
IngNameID
IngSupplier
IngLot#

tblIngDetail
DetailID*
BatchID
IngNameID
IngSourceID

*Primary key

So tblIngDetail bridges the primary keys from the other three tables. I
hope you can visualize this. It seems rather complicated to me and I'm not
sure its the best set up.

The problem I have is with the form. When I set up CBO2 (supplier) to be
filtered by CBO1 (Ingredient; filtered for IngNameID =CBO1 using the SQL for
row source) with a requery for CBO2 in the On Change event for CBO1, it
filters all the CBO2, not just the one for that record. So say for Batch No
123 I selected Ingedrient 1 in CBO1, CBO2 would list suppliers A & B. I go to
the next line and select Ingredient 2 in CBO1, CBO2 is filtered for its
suppliers (C &D), but CBO2 for Ingredient 1 is now blank because it doesn't
have the same suppliers as Ingredient 2!!!!! Please help!

 

[Arvin Meyer [MVP]]

Seems pretty easy to me, but where does tbllngName come in? BTW, adding lng
(which I presume is a reference to a long integer) is unnecessary at the
table level, but very helpful when using variables in code.

BatchID is only necessary if you have a description as well, since a year
from now no onewill have any idea what BatchID 27 is. So, without tblName,
the structure needs to be:

tblBatch
BatchID
BatchDescription

tblSupplier
SupplierID
SupplierName

tblSource
SourceID
SupplierID
LotNumber

tblBatchDetail
BatchDetailID
SourceID

The combo boxes are similar to the list boxes in this sample database:

http://www.accessmvp.com/Arvin/Combo.zip

 

[Ken Sheridan]

Correlated combo boxes where the value is an unseen surrogate key (often an
autonumber), don't work well in continuous forms as you've found out. You
have two alternative solutions to this:

1. Use 'natural' keys so that you don't have to hide the bound column.
Even when the control's is requeried for one row so that its RowSource does
not return the value of the key, it will still show in the control in other
rows. With natural keys its important that cascade updates are enforced of
course, to cater for possible changes to the value of a referenced primary
key column.

2. Where surrogate keys are unavoidable, e.g. with personal or place names
which can be duplicated, then you can use hybrid controls where a text box is
superimposed on a combo box to give the appearance of a single combo box
control. I've posted a demo of this at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local government areas of parish, district and county in my part
of the world, but the principle is the same for all data in hierarchical
relationships. As you'll see if you do download the file I'm not very keen
on the use of such controls in continuous forms. Performance is not
lightning fast, and there are some aspects of their behaviour which can be
slightly confusing to the user. With two levels to the hierarchy rather than
the three of my demo the objections are not so great, however. The demo does
include an alternative approach using a bound multi-column combo box and
unbound text boxes.

Ken Sheridan
Stafford, England

 

[Gen]

I see, so its not just me this really is complicated! I'll go through your
sample database to try and come up with a solution. Thanks for your advice.

 

[Gen]

Sorry for the confusion, Ing is actualy short for for Ingedient. tblBatch
holds the general Batch information, tblIngName holds the names of all
possible Ingredients and descriptions of them, tblSource holds the supplier
info and lot# for each order of ingredient and tblIngDetail brings together
the Ingredient name with the supplier and lot# used in each batch.

I only included the relevant fields, each table has much more information of
course.

 

[Gen]

Ken,

I noticed that the relationships in your database are set up in a linear
fashion. In my database, three tables are connected to one (tblIngDetails).
Do you think this is best, or should I set it up so that tblBatch ->
tblIngDetails -> tblIngNames -> tblIngSource? I think I will design the sub
form as you suggest in the thrid example - using multi-column combo boxes and
text boxes.

 

[Arvin Meyer [MVP]]

So add tblIngName with

IngNameID
IngName

then add IngNameID to the Source table
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Ken Sheridan]

The models are different. In my case it’s a straight hierarchy with County
at the top and Parish at the bottom and one-to many-relationship types in
each case; in your case there is a many-to-many relationship type between
Ingredients and Suppliers. A many-to-many relationship is modelled by a
table, IngredientSuppliers say, which references the primary keys of the
other two tables. There is also a many-to-many relationship from
IngredientSuppliers to Batches, however, so another table, lets call it
BatchIngredients is needed which references the composite primary key of
IngredientSuppliers and the primary key of batches.

Ken Sheridan
Stafford, England