Multiple Selection Options - table layout?

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

Guest

I want to put a sensitising field in my table (and form) that contains
various options.
However of about 10 different options any number can be selected.
For example
Item 1 can contain sensitising agent 1,4,7 and 8
Item 2 can contain sensiting agent 1,5,and 9
Item 3 can contain sensitising agent 1-10 inclusive

Can i put this into 1 field on my (product specification) table or will the
table have to have multiple fields like Sesitising agent 1, Sesitising agent
2, Sesitising agent 3 etc and yes/no option boxes beneath them?
 
WoodyAccess said:
I want to put a sensitising field in my table (and form) that contains
various options.
However of about 10 different options any number can be selected.
For example
Item 1 can contain sensitising agent 1,4,7 and 8
Item 2 can contain sensiting agent 1,5,and 9
Item 3 can contain sensitising agent 1-10 inclusive

Can i put this into 1 field on my (product specification) table or will
the
table have to have multiple fields like Sesitising agent 1, Sesitising
agent
2, Sesitising agent 3 etc and yes/no option boxes beneath them?
Click to expand...

Neither.

You should have 2 tables: one that has whatever data is currently in the
table, linked to a second table that has 1 row for each sensitising agent
associated with that entry.

From a data entry perspective, you'd have a form with a subform on it. Take
a look in the Northwind database that came with Access: the Orders / Orders
Subform setup is basically what you're looking for.
 
Im a little confused should the 2nd table just have one column that lists the
sensitising agents or should the individual sensitising agents be the fields
themselves?
 
Assuming your first table has a single field as the Primary Key to represent
the Item to which it refers (let's call it ItemID), and you have a table
that holds the details for all of the Sensitising Agents, and that table has
a single field as the Primary Key (let's call it AgentID), the table in
question would need to have 2 columns: ItemID and AgentID.

For your specific example:
Item 1 can contain sensitising agent 1,4,7 and 8
Item 2 can contain sensiting agent 1,5,and 9
Item 3 can contain sensitising agent 1-10 inclusive
Click to expand...

the data would look something like:

ItemID AgentID
1 1
1 4
1 7
1 8
2 1
2 5
2 9
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
3 10

This is the standard way to resolve a many-to-many relationship (each item
can have many sensitising agents associated with them, each sensitising
agent can be associated with many items). If you have problems with the
concept, you might want to check some of the references Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
 
Thanks Douglas J. Steele I have learnt a bit about many to many relationships
by doing this.

However, what I am looking to do is to put the 10 options of agents onto the
original parent item form (already created) as check boxes and once these
check boxes are checked all the information to be updated on the many-to-many
table? Is this possible?
 
Not easily.

There are techniques you can use to create a query that will convert the
setup discussed to a query that includes ten Yes/No fields to correspond to
the ten sensitising agents. However, any such query wouldn't be updatable,
so you'd have to put all sorts of logic in your form to handle the updates
manually, rather than having on Access take care of all that for you.

You're best off using a form/sub-form arrangement.
 
However, what I am looking to do is to put the 10 options of agents onto the
original parent item form (already created) as check boxes and once these
check boxes are checked all the information to be updated on the many-to-many
table? Is this possible?
--
Click to expand...

You would need to use ten unbound checkboxes and some (fairly snarky)
VBA code to poll through the checkboxes and update the table. I'm with
Douglas - why not use the very standard subform approach? One
advantage is that if you ever need to add an eleventh option, it's
easy and you don't need to redesign your form!

John W. Vinson[MVP]
 
I'm a complete novice at the VBA code so i'll give that a miss and go for the
subform.

I'm not quite sure how to work putting the subform in. The main form is a
product specification form which works off the primary key of "Product".
However I have had to create a separate table for Agents (primary key -
"Agents") because various selections were needed. And then a table with
many-to-many relationship with foreign keys "product" and "agents".

However I am trying to put in a suform for a many-to-many relationship table
into a form for one of its relationships (ie the product). What I want to
happen is that when filling out the product information (main form), the
product combo box control on the main form is linked to the product combo box
control on the sub form so that when filling out information the many-to-many
relationship is automatically updated.

I hope this maked sense
 
I'm a complete novice at the VBA code so i'll give that a miss and go for the
subform.

I'm not quite sure how to work putting the subform in. The main form is a
product specification form which works off the primary key of "Product".
However I have had to create a separate table for Agents (primary key -
"Agents") because various selections were needed. And then a table with
many-to-many relationship with foreign keys "product" and "agents".

However I am trying to put in a suform for a many-to-many relationship table
into a form for one of its relationships (ie the product). What I want to
happen is that when filling out the product information (main form), the
product combo box control on the main form is linked to the product combo box
control on the sub form so that when filling out information the many-to-many
relationship is automatically updated.

I hope this maked sense
 
However I am trying to put in a suform for a many-to-many relationship table
into a form for one of its relationships (ie the product). What I want to
happen is that when filling out the product information (main form), the
product combo box control on the main form is linked to the product combo box
control on the sub form so that when filling out information the many-to-many
relationship is automatically updated.
Click to expand...

You don't need, and shouldn't have, a control bound to the ProductID
on the subform. Instead, use the ProductID as the subform's Master and
Child Link Field - it will automatically fill in the current productID
from the mainform.

John W. Vinson[MVP]
 
No point having multiple threads going on the same question.

Hopefully John's answered your question to your satisfaction.
 
Back
Top