Multiple Options On one record ties to a look up table

[Gerald]

Ok all I am sorry for the very basic question but I am lost and seem to have
hit a brick wall..

I am trying to create the ability to select multiple records off of my
lookup table and store the selections in my main table as number (i.e)
autonumber of lookup table.

The next stage I am trying to get done is addding some form interface
options that show the selections (i.e. button or check box) so that user can
easily see the avail options to select or that have been selected on any
given record.

Any help is greatly appreciated.

Gerald

 

[John Vinson]

Ok all I am sorry for the very basic question but I am lost and seem to have
hit a brick wall..

I am trying to create the ability to select multiple records off of my
lookup table and store the selections in my main table as number (i.e)
autonumber of lookup table.

Are you trying to store multiple values *in one field*?? No can do!

If you are using a Lookup Field in your table... don't. See

http://www.mvps.org/access/lookupfields.htm

for a critique of this misfeature.

The next stage I am trying to get done is addding some form interface
options that show the selections (i.e. button or check box) so that user can
easily see the avail options to select or that have been selected on any
given record.

If you have a Many (records) to Many (options) relationship, you need
another table to contain that relationship. The classic example can be
seen in the Northwind sample database Orders form; each Order may have
several Products, and each Product can be in many Orders. This is done
by using the OrderDetails table with fields for the OrderID and
ProductID; you'll add a new record for each product.

Similarly, if you have a main table and each record can have multiple
options, you'ld have a table with a link to the primary key of your
main table, and a link to the primary key of the lookup table of
Options.

John W. Vinson[MVP]

 

[Guest]

Oops!

SELECT not SEWLECT!

Ken Sheridan
Stafford, England

 

[Gerald]

Thanks for all the options and I think ths may work as well now that I had
some sleep and cleared my mind lol:

I set up a talbe with the basic atributes that I am trying to link together.
It is a small finite list that will change very seldom then on my main list
have a field that stores a numeric value. The using a query referance then
number in main table to the sub table I created but not linked for reporting
purposes. The input and record modifications on the input form will be
simply selectiont the options you need and then the vaule assigned by me
will be entered into the record or removed from record depending on the stat
of the toggle button or slection made.

For example:
My main table has Item Num (Primary key), Product name, as well as a key to
compents table.

The Components table has a primary key used to link table to proper product.
Then a list that is created as we pu t in the components needed to make an
item (this refers to ingredient table for all supplies that we could
possibly need in the manufacture process.

Ingrediant Table
This is where line item information is put in for all supplies needed to
manufacture components as previously talked about. The problem was There
are times several suppliers that we can order the particular supply we are
needing and the ability to have more than one showing per item is needed so
we can easily contact all the vendors who offer the item.

So I hope this option I spoke of earlier will allow for the last part of
this long drawn out explantion but still like any input regarding this
matter thanks for what has been offered and what may come yet.

 

[Guest]

Gerald:

Forget about the interface for a moment and examine the table structures.
Getting these right is the first essential. Lets take things one by one:

If each component can only be in one product the relationship here is
one-to-many from Products to Components, so there should be no foreign key in
Products referencing components, but a foreign key ProductID column in the
Components table. If, however, each component can be in more than one
product then the relationship is many-to-many so you need another table to
model the relationship, ProductComponents say, with foreign keys ProductID
and ComponentID.

The same principles apply: if each ingredient can only be in one component
the relationship here is one-to-many from Components to Ingredients, so there
should be a foreign key ComponentID column in the Ingredients table. If,
however, each component can be in more than one product then the relationship
is many-to-many so you need another table to model the relationship,
ComponentIngredients say, with foreign keys ComponentID and IngredientID.

This is a clear cut many-to-many relationship between Ingredients and
Suppliers, so a table, IngredientSuppliers say, is needed to model it, having
IngredientID and ProductID foreign keys columns.

So the first thing you need to do is decide on what type of relationships
there are between Products and Components, and between Components and
Ingredients. Then you can create the tables appropriately on the basis of
the answers to these questions, and design an interface which reflects the
'logical model'.

You might find the above easier to follow if you draw the structure out
diagrammatically as an 'entity relationship diagram', with a box for each
table and lines connecting them representing the relationships. The
many-to-many relationships should not be shown directly but resolved as two
one-to-many relationships, e.g.

Products----<ProductComponents>----Components

where the < and > characters represent the 'many' side of the relationship.

Now I'm going to throw another spanner in the works. The above comments are
on the basis of your description, i.e. that each product is made up of one
level of 'components' each of which is made up of a number of atomic
ingredients. However, another possible scenario is that a 'component' might
be made up of other components and so on, with only the bottom level
components being made up of atomic ingredients, i.e. the components are
assemblies which are made up of sub-assemblies, which in turn are made up of
other sub-assemblies and so on until the bottom level of the atomic parts
making up the assemblies is reached. This would require a very different
logical model. The usual model for this is to have a table Parts which
includes all atomic parts and all assemblies in the one column. A second
table PartStructure say is an 'adjacency list' having columns MajorPart and
MinorPart say, both referencing the key of Parts.

With the above model to produce a 'bill of materials' or 'parts explosion'
for a product or assembly requires recursive querying with a variable number
of calls of the query. There are a number of theoretical approaches to this,
but none which can be directly reproduced in a real query. It is possible to
simulate it in Access and I published a means of doing so in a magazine
article some years ago. The demo file is no longer on their site, however,
but I'd be happy to mail it to you if you wish.

Ken Sheridan
Stafford, England