Similar option to Multivalue Lookup Fields (1 Viewer)

Is there another field that can be used beside the lookup field in an Access table? I am currently using the lookup field as a multivalued lookup field and I am limited to the the things I can do with it when creating a report or a query on that field. Is there a similar field in Access 2010 that has the same look as a lookup field and allows for multiple values to be selected?

lrarmbruster, Table level Lookup is an absolute NO NO !!

Tables are not supposed to store multiple values, this is the breach of the fundamental rules of Normalization.. Which states..

Normalization said:

Data should be "atomic" That means that data should be stored in its most basic, indivisible form; for example, avoid storing an address as a single data element, with street number, street name, apartment number, city, state and zip code. Each of these are separate pieces of data and should be stored in separate columns.

Click to expand...

When you are desigining tables, the question is not "how many of something do I have", it is "do I have more than one of this thing". If you have more than one, it belongs in a separate "child" table. A student takes "many" classes. A student has "many" pets. A student has "many" vehicles. Each of those "many" pieces of data require a separate table all related to student via StudentID.

The "Lookup & Relationship" field is referencing another table for its values. Access 2010 allows a lookup field to have more than one value selected. My problem is that when I use this field in an access query I am not able to link the lookup field to another table because of it is considered a multivalued lookup. Without being able to link the two tables in a query when I create a report I am given the ID value instead of the text value. I want to see the text instead of the ID number. I want to know if there is a way to get the text to display or if there is a different field I can use that works the same as a lookup field that allows for multiple values. I am using web queries and web reports because this is being uploaded to SharePoint. I can make the query work if I do not use a web query but then I have the problem that it is not web compatible and cannot be uploaded to SharePoint.

Lookups are an abomination forced on Access in an attempt to be compatable with Sharepoint. If you set up your own tables and relationships, you will have better control over the situation. What you give up is the ability to use the multi-select checkbox. You will need to use a subform instead.