How do I select and store multiple values from a lookup list

[Mike A]

How do I select and store multiple values from a lookup list

 

[Douglas J. Steele]

If you mean you want to store them in a single field, you really shouldn't
(it's a violation of database normalization principles to have more than one
value in a single field)

What you should have is two tables: one containing the main record, and one
containing as many rows as there are objects in the lookup list, each with a
foreign key pointing back to the related main record.

 

[Mike]

Actually I already have the lookup table set up as you suggest. What I want
to do now is have a field that will hold multiple values selected from the
lookup table. This is for ad run dates for a publication. We need to be
able to select multiple individual dates for a specific ad to run and would
prefer to select multiple dates at once as opposed to having each date take
up an individual field. THANKS

 

[Douglas J. Steele]

Store it in separate fields, and then have a function such as what's given
in http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" for
reporting purposes.

It's far better to have your data stored correctly, rather than compromising
the design for a specific use.

 

[Rick Brandt]

Actually I already have the lookup table set up as you suggest. What I want
to do now is have a field that will hold multiple values selected from the
lookup table. This is for ad run dates for a publication. We need to be
able to select multiple individual dates for a specific ad to run and would
prefer to select multiple dates at once as opposed to having each date take
up an individual field. THANKS

Read Douglas' reply again. He is not talking about a separate "lookup
table". He is talking about a separate table to store the "entries" (you
need 3 tables).

The most common example is an order form. One table for the main data
(customer, date, order number, etc.) and another table for the items
ordered. You wouldn't have an order form with just one big memo field to
contain the list of items ordered. You have a separate table with a
separate form (usually embedded in a subform). That way each "order" can
have from zero to (however many) items associated with it.

Your dates should be handled the same way.

 

[John Vinson]

Actually I already have the lookup table set up as you suggest. What I want
to do now is have a field that will hold multiple values selected from the
lookup table. This is for ad run dates for a publication. We need to be
able to select multiple individual dates for a specific ad to run and would
prefer to select multiple dates at once as opposed to having each date take
up an individual field. THANKS

Just to reemphasise Douglas' and Rick's suggestions:

Don't store multiple dates in one field.
Don't store multiple dates in one record *either*.
Store each run date in a NEW RECORD of an additional table, AdRunDates
say. If the ad is to run six times, this table would have six records
for that ad.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps