Multi Value Fields

[lyncha]

Hi guys

I am looking for some advice on how best to capture multi value data for a particular field.

Basically I am working on a project in Access 2010 whereby I need to provide users with an option to select multiple values for a selected field but I am struggling on how best to achieve this.

The DB is set up as follows…

Table1 – Static Data
SK Number
Company
Audience

Table2 – Job Details
SK Number
Status
Owner

Table3 – Company (Used as a Lookup Table)
Record1
Record2
Record3 etc

Form1 – bound to Table1 to add and amend static records

Form2 – bound to Table2 to add and amend job details. This form also has a sub-form to show (read only) the records from Table 1 where the SK Number matches.

Form3 – bound to Table3 to allow users to add and amend records used within a lookup.

Within Table1, the field ‘Company' can have multiple records and I am trying to allow the users the ability to select these from a drop down list.

I was looking at the ‘Lookup’ functionality available in 2010 but this does not meet my requirements as I need to import data into the table and this would not be possible using this option.

I also toyed with the idea of creating a separate Field in Table 1 for each country and then applying a checkbox on the form but there are too many records.

One option I was considering was using a list box but the layout of this for selecting records would not work with the suers due to their being such a wide choice.

Does anyone have any suggestions on how best to achieve this.

Many thanks

[orange]

If you search the forum and look for comments related to
a) multiple valued fields and
b)Lookup tables

You will find recommendations for a) that you should avoid multiple valued fields that m$oft has made available. They violate normalization principles and will get you into situations that can be avoided by not using multivalued fields.

If your intent is to use lookups at the field level in a table, the general advice is to avoid using lookups at the table field level. see http://access.mvps.org/access/lookupfields.htm for details.

You have told us how you have set up some tables, but you have not told us in plain English what you are trying to do.
If you would describe to the readers WHAT you are trying to do, I'm sure you will get some answers with recommendations and advice.

[lyncha]

Thanks for the reply.

Basically I want to allow the user the ability to select multiple items from a list and update a table based on the selections they make.

The more I read the more I feel that the only option is to use a list box.

[orange]

ability to select multiple items from a list ---What list? Where do the "selectable things" come from?

[lyncha]

Thanks Orange,

The records are held in a seperate table called 'Company'. The users should be able to select one or many records from the 'Company' table via a drop down list on a form and then the records they select update the 'Company' field in a seperate table called 'Static Data'.

The DB is set up as follows…

Table1 – Static Data
SK Number
Company
Audience

Table2 – Job Details
SK Number
Status
Owner

Table3 – Company (Used as a Lookup Table)
Record1
Record2
Record3 etc

Form1 – bound to Table1 to add and amend static records

Form2 – bound to Table2 to add and amend job details. This form also has a sub-form to show (read only) the records from Table 1 where the SK Number matches.

Form3 – bound to Table3 to allow users to add and amend records used within a lookup.

[June7]

Add my voice to the chorus, avoid multi-value fields. Unless this is a web database there is virtually no justification for using.

Should have a child table to save detail records. Create a form/subform arrangement for data entry.