Access Multivalued fields

Question

I am trying to create a multivalued field with 3 entries. In another field I'm wanting to input a value depending on what they choose from the previous field. For example if they choose option 1 temps it will put 63 in the number of training days field, if they choose option 2 precdl it will put option 56 days in the number of training days field, and option 3 CDL will put 42 days in the number of training days field.

Fields Type of Training # of Training Days

                   Temps                               63

                    PreCDL                             56

                     CDL                                  42

So Type of training is the mulitvalued field with 3 options and depending on which option they choose the # number of training days will be inserted automatically in the # of Training Days field.

Answer 1

What you describe does NOT appear to be a Multivalued field (MVF). A MVF is a field that can store multiple values in one field. It doesn't appear that's what you want. Instead, what you appear to want is to assign a Type of training to a record, then assign the number of training days for that record. So you are only assigning ONE value. What you appear to want is a combobox on a form that looks up against a type of training table like so:

tblTrainingType

TrainingTypeID (PK autonumber)

TrainingType

TrainingDays

This combobox should be done on a form, NOT in the table. And the field in the record you are populating should hold the TraingingTypeID as a foreign key.

Unless the number of training days can change over time or be overridden you don't need to store the # of days, you can pull that value from the type of training table.

Answer 2

A multi-valued field models a many-to-many relationship type.  While, at first sight, this might be seen as a convenient way to do so, one of its various shortcomings is that you cannot include any other attributes of the relationship type.  For this reason most experienced developers eschew the use of this feature.

You might like to take a look at MVFCorrector.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.

This little demo file illustrates how to recast the data from a MVF into a table which models the many-to-many relationship type in the conventional way by a table which resolves the relationship type into two one-to-many relationship types.  It includes examples for where the MVF's possible values are stored in a table or in a value list.

Once the data has been recast into a correct structure you should have no problem including the number of days.  Note that by including the number of days in the table which models the relationship type, this will allow the default number of days per training type stored in the referenced TrainingTypes table to be subsequently amended if necessary while leaving the values already stored in the table modelling the relationship type unaltered. The appropriate current value should be assigned to the column in the table modelling the relationship type in the AfterUpdate event of a combo box control in which the training type is selected.

Answer 3

I see nothing but dark clouds in the future for this database.

Although you can force them to work some limited fashion, wisdom warns that there is a better way than resorting to attachment fields.

Create a lookup table with three fields and three records. Call it "TrainingRequiredDays" or whatever suits you best.

The fields will be these, but choose your own names.

TrainingTypeID -- This is your Primary Key. Use the AutoNumber for this one

TypeOfTraining -- Short text for the names, Tempts, PreCDL and CDL

NumberOfTrainingDays -- Integer for the numbers 63, 56, and 42

Now, in the table where this unfortunately all too easily available attachment field appears, remove it.

Replace it with a field called "TrainingTypeID". This field is Foreign Key for the related record in the TrainingRequiredDays table.

In the FORM where you add records, put a combo box bound to TrainingTypeID. Make the rowsource for this combo box the shinynew "TrainingRequiredDays" table. Now, when you select a value in the combo box, you get the TypeofTraining AND the NumberofTrainingDays.

Done.