store multiple answers from a drop down list

[KarenF]

Hi,

Using Access 2003 we are designing a database to monitor deliveries. We
have used the look up wizard to define categories of delivery and were
wondering if it is possible to store more than one entry from that list in
the field we are populating?

If anyone can help, we will be very grateful.

Many thanks,

Karen

 

[Carl Rapson]

Hi,

Using Access 2003 we are designing a database to monitor deliveries. We
have used the look up wizard to define categories of delivery and were
wondering if it is possible to store more than one entry from that list in
the field we are populating?

If anyone can help, we will be very grateful.

Many thanks,

Karen

You can't store more than one value in a field in Access 2003. If you need
multiple values, consider setting up another table in a one-to-many
relationship with your main table.

Carl Rapson

 

[Klatuu]

Contatenating multiple values into one field in one record is always a very
bad idea.
You stand to grow the length of the field beyond the 255 byte limit.
It is difficult to retrieve a unique value from the field.
Using the field to do any kind of lookup is impossible.
In a case where you need multiple values for an item associated with one
record in a table, the correct answer is a child table related to the main
table. Set it up as a one to many relationship, and you can have 0 to many
values associated with the record.

The second problem you have is that you cannot select multiple items from a
Combo box. To do this requires a List Box. If you create the child table
and make it the record source for a subform, then you can use the combo to
look up each value separately.

 

[John W. Vinson]

Hi,

Using Access 2003 we are designing a database to monitor deliveries. We
have used the look up wizard to define categories of delivery and were
wondering if it is possible to store more than one entry from that list in
the field we are populating?

No. That would be EXTREMELY bad design! Fields should be "atomic", having one
and only one value.

If each Delivery can be in several categories, and each Category can apply to
several Deliveries, you have a "many to many" relationship. The proper way to
handle this is to add *a new table*, DeliveryCategories; it would have foreign
key fields to link to the primary key of the Deliveries table and to the
primary key of the Categories table (which might well just be the category
text itself). You can use a Subform based on this table, with a combo box to
select categories; each category would be in a separate record on the subform.

If you're using Lookup Fields in your table - or for that matter editing data
directly in the table - don't. Forms are your friends. For a critique of
lookup fields see

http://www.mvps.org/access/lookupfields.htm

By all means use Lookups - combo boxes - but use them where they belong, on
your Forms.

John W. Vinson [MVP]

 

[Joseph]

http://www.mvps.org/access/lookupfields.htm

By all means use Lookups - combo boxes - but use them where they belong, on
your Forms.

In relpy to your answer and after looking at that link above, can you
explain to me another aproach to linking tables other than lookup fields? I
may not be new to access, and I am not claiming that I know alot about
access, but I have built several databases for personal and private(military)
use. In these databases, I use look-ups to link the tables, is there a
different way? And if so, please explain, because it could possibly help me
in my current endevor.



My current task: I work for a Juvenile Boot Camp that has no formal computer
infrastructure or instruction. I have been voluntold to record all
information in a format that is able to consolidate the information as well
as be consistent with the format of reports and the way data is stored. So
far I have seven databases all linked together in some form or fashion via
look-up fields. Now while some of these databases will not grow as fast as
others (StaffInfo vs DailyProgressRpts), they will eventually need to be
upsized and archived (to make current db smaller). On your link, with
look-ups, the db will not be able to be upsized, which is a major concern to
me. Thus, returns us back to the original question, is there a different
aproach to the "look-up field"?

 

[Klatuu]

Create your own tables that carry the information. Relate them to the tables
you want to use the look up for. Then use a combo box with it's after update
and not in list events to populate the fields in your table.