Multiple selections from list

[CherylB]

I am trying to determine how best to choose multiple selections from a list
and I'm having difficulty. I have a list of approx. 50 acronyms in a
seperate table within my database, I would like to be able to choose from
this table to make entry easier for the user and to avoid typing errors
however using the look up feature only allows me to choose one. Creating
multiple fields is not an option, some rows might contain 25 acromyms. I
realize that doing this limits me from sorting, etc and that's OK - I just
need to find a way to list a row of the information. Someone suggested that
a list box would work better than the lookup feature but I couldn't make that
work either.

Any suggestions would be very much appreciated as I'm working with a fast
approaching deadline.

Thanks

 

[Duane Hookom]

If you want to store acronyms with a main record, you should use a junction
table. For instance, if you have the FBI's most wanted list and each of the
top ten criminals had aliases, you would create a related table like:

tblCriminalAliases
=================
CrimID Alias
1 Left
1 Second Story
1 Butch
2 Snitch
3 Duke
3 Animal

Lookup fields are evil.

 

[CherylB]

I'm not familiar with what a junction table is. I have my main table, the
field I want to feed is called "Sponsoring Dept", the info is in a seperate
table called Organization which contains 3 columns: ID, Org and Acroymyn.
Both the main table and the Organization table have ID as primary keys. I'm
not sure what to do from here.

Also, in your example I'm not sure what the numbers are referring to.

Thanks!

 

[Duane Hookom]

You would create another table:
tblMainOrganization
====================
MainID value from main table ID
OrgID value from the Organization.ID
Every different organization associated with a record in your main table
would create a new record in tblMainOrganization. This is called a junction
table. It's not all that different from the OrderDetails table in Northwind
where the "main" table in Northwind is the Order table and the
"organization" table in Northwind is the Products table. Any one order can
contain multiple products. Each product in an order creates a new record in
OrderDetails.