Managing multivalue fields

[AccessDenial]

Understand Access has multivalue fields but its causing me some issues when implementing other features such as audit trails. Besides it is not a norm in database administration? Hence, I'm trying to do it the "right way" by creating separate tables. The following is an example of say recording the type of fruits my employees like.

Fruits Table:
NUM...FruitPreference
1...Apple
2...Pear


3...Orange
4...Apple Pear
5...Apple Orange
6...Pear Orange

Employee Table:
Name...NUM
John...2
Paul...4
Mary...6

I created my form and naturally I do not want the user selection to be based on NUM nor the entire list of fruit preference (imagine if there are more combinations and everything must be listed). Ideally, it should just be Apple, Pear, Orange and the respective number returned to the NUM column in my Employee Table depending on which combination they choose.

Is this possible?

Thanks in advance.

[ranman256]

Dump the multivalue field method, (I never use it)
Its much easier to just use a subTable. 1-many

NUM...FruitPreference
3...Orange
4...Apple
4...Pear
5...Apple
5...Orange
6...Pear
6...Orange

[AccessDenial]

Hi! Could you elaborate more on how to do a subTable please? I don't understand your example. Sorry!

[ranman256]

its just a table with the Key (NUM) from the master table.
if you want the subTable to have NO duplicates , then you must key both fields in tFruitOwned:
num*
FruitPreference*

then in Relationships, join keys: Employee.Num to tFruitOwned.Num

(now you can have many fruit to the 1 employee , without duplicates)

[ssanfu]

In its simplest form, you need 3 table:
a table for Employees
a table for Fruits
and a junction table to hold the selections.



You would use a main form/sub form to make the selections.
You could have a main form for the employees and the sub form to select the fruits
OR
you could have the main form select a fruit and the sub form to select the employees that liked that fruit.

If you did not want John and Banana to be selected more that once, you would set a compound INDEX (not a compound PK fields) on the fields EmployeeID_FK and FruitID_FK. See Microsoft Access Tables: Primary Key Tips and Techniques

[davegri]

Here's a working skeleton model. People main form and Fruit subform. Many-to-many table setup.

MTM with subform-davegri-v01.zip