Selct multiple records from a lookup table and store in a field

[Darrell]

Using Access 2003, table one is the main table for storing report
information. Table two is a lookup table with records to be able select a
specific unit for the record in table one. The unit data selected from
table two is stored in a single field in table one. However, there is a
need to be able to select multiple records from table two to be stored in
the field in table one. How can this be done?

Darrell

 

[Duane Hookom]

This is not easily possible in any current version of Access. Normally you
want to create a junction table that stores the primary key values of table
one and table two. You would add values/records using a continuous subform.

 

[Darrell]

I'm new at Access developement, so could you point me in the right
direction? For instance, when the lookup table is opened, I want to be
abelto press ctrl + click the entry to select all that apply.

Darrell

 

[Duane Hookom]

You won't be able to get the same functionality/interface without
significant effort.

Take a look at the Northwinds and see how multiple products are added to a
single order. If you can't figure this out, come back with some signficant
fields and table names.

 

[Darrell]

OK,

Table one is called Assessment. Table two is called units. Table one has a
field called program(s) that holds the information from the units table.
Right now, I add information to the program field in table one from the
units table via a lookup function.

Darrell

 

[Duane Hookom]

You need to create a junction table that contains the primary key values
from Assessment and Units. I think it would be a huge mistake to attempt to
store multiple values in a single field.

 

[Darrell]

Thanks for the info. I will go and learn about this, to get a better
understanding of proper design.

Darrell