Multiple selections from forms

[TP]

I have searched through many responses to what appears to be my query, but
still cannot find a suitable solution.

I am trying to create a database to consolidate standard risk assessments
and generate a report based on some simple input from the user.

I have the following simple tables
Activities
Tasks
Hazards
Effects
Severity

Activities can have many tasks, tasks can have many hazards, hazards can
have many effects and effects can have variable severity ratings.

I have tried to concentrate on just sorting Activities and Tasks for now and
have created a Junction table (Activity_Task) to give a many to many
relationship and a form/subform to enter data using a combo box. My question
is how to select many tasks for 1 activity at the same time and put them in
the juntion table without having to use the drop down combo 1 line at a time.
The feature in Access 2007 for multiple selections looks great ( Yes I know
it's bad), but then I can't subsequently add many Hazards for each Task so I
want to do it the traditional way using tables.

Any ideas gratefully received.

 

[Philip Herlihy]

I have searched through many responses to what appears to be my query, but
still cannot find a suitable solution.

I am trying to create a database to consolidate standard risk assessments
and generate a report based on some simple input from the user.

I have the following simple tables
Activities
Tasks
Hazards
Effects
Severity

Activities can have many tasks, tasks can have many hazards, hazards can
have many effects and effects can have variable severity ratings.

I have tried to concentrate on just sorting Activities and Tasks for now and
have created a Junction table (Activity_Task) to give a many to many
relationship and a form/subform to enter data using a combo box. My question
is how to select many tasks for 1 activity at the same time and put them in
the juntion table without having to use the drop down combo 1 line at a time.
The feature in Access 2007 for multiple selections looks great ( Yes I know
it's bad), but then I can't subsequently add many Hazards for each Task so I
want to do it the traditional way using tables.

Any ideas gratefully received.

I wonder if you could use a List box instead of a combo? That has the
capability of using shift-click or control-click to make multiple
simultaneous selections.

Phil, London

 

[Philip Herlihy]

I wonder if you could use a List box instead of a combo? That has the
capability of using shift-click or control-click to make multiple
simultaneous selections.

Phil, London

PS - you have to set the appropriate property to allow this.

Phil

 

[KARL DEWEY]

For each of these -
Activities
Tasks
Hazards
Effects
Severity
do you have a predefined list for them?
If so, then it seems to me you need an Incident table with one-to-many
relation to Incident_Activity table using a form/subform with combo box to
select Activity.
Then a subform for one-to-many Activity - Task having foreign key field
related to Incident_Activity table on Activity.

A hazard can have multiple effects but for a single incident the efect of a
hazard can have only one severity rating.

 

[TP]

to Karl,

Thank you for your prompt response.
Each table other than severity (fixed list 1 -5 but still in a table) does
not have a predefined list and must be able to be added to and updated i.e.
Tasks and Activities will continually be added and refined. Severity although
as you say will only have a single rating for a particular activity, may well
change for a different Activity or situation and so needs to be defined at
run time by the person generating the Safety document (report)

The table/subform I have working perfectly well for data entry, however by
using a combo box I have to select each task associated with a single
activity, a single line at a time. I would prefer to have a varaible list
based on tblTasks to allow me to select all the relevant tasks (which may be
quite a lot) in a simple operation by highlighting or ticking a check box,
which is why A2007 feature is attractive. I can create a list box to
highlight several values from a table at the same time, but I am unsure how
to save the multiple selections to a separate table in their own separate
field. They need to be in separate fields because I then need to cascade that
process down (i.e. the Tasks previously selected for each Activity will each
then have multiple Effects associated with them and so on) I have achieved
what I need using A2007 but cannot then do the cascaded process further down,
probably because I can't get at the actual data fields where the selections
are held.

 

[KARL DEWEY]

does not have a predefined list and must be able to be added to and updated
i.e.
The combo using query instead of predetermined list will be constantly
updated.
Worng, what you are describing is a spreadsheet. They need to be saved to
individual records.