multiple values in access 2007

[phillbaker]

i have been trying to create a field that will store multiple values but when using the lookup wizard as suggested after i click the table i want it to get the values from it doesnt give me the check box for allowing multple values.

has anyone else had this problem or what am i doing wrong.

phill

 

[ajetrumpet]

looking up values in tables themselves is a bad practice. please try going about it a different way.

 

[phillbaker]

what other ways do you think are best practice.

 

[boblarson]

Actually, the lookup at table level is one of those things that we've discouraged in the past as it causes serious problems later when trying to use things:
http://www.mvps.org/access/lookupfields.htm

The new "multi-value" field is one thing where I don't think many developers have embraced at this point because getting the values out is not all that easy when you go to do a normal query, etc. It actually stores the data behind the scenes in basically a junction table (what you would normally use for a many-to-many situation for normal programming). But you have to do some code which is not very prevalent yet to access it. If you do some searches for Albert D. Kallal (he's an Access MVP), he has taken the jump into working with them some and there are some posts of his out there (not on this site) that will help you with some of that.

But, as Adam was pointing out - for the majority of us we just continue to use another table and a subform for the multi-values that we want to store for a record on a master form.

As for why you don't have the ability to have the multi-select available to you - you perhaps are not storing the correct data for this. I don't have 2007 at work yet, but if I remember correctly I think you have to select a long integer type and then it becomes available in the lookup dropdown on the lookup tab in the table.

 

[ajetrumpet]

what other ways do you think are best practice.

a lookup procedure is not bad phil, but the lookup WIZARD in access IS bad. it's not a useful tool. Lookup tables are commonly used to store values that are not geared towards being linked to other data (they are "loner" data, so to speak).

that said, Here's a typical accounting setup where a lookup table would be useful:

table 1: invoices (id, invoicenum, customer, terms)
table 2: terms (lookup table.) <---this is never part of your table relationship stucture.

you populate the table 2 with one column of values to be ultimately used in a combo box on a form for data entry in your invoices table. values that you would find in the lookup table in this case would be those such as:

*net 30
*net 20
*1% net 10
*etc, etc,...