【 Create Multivalue Lookup Column in Access 】 Guide ▷ 2022

It allows create a column of multiple values. This will help you display different items at the same time via a dropdown list.

So that you can take full advantage of the multivalued function of Access, you will have to know what it is for and what are the most important benefits which has to use a multivalued lookup field.

You will find this information in the following paragraphs, as well as you will be able to know the step by step that you must carry out to create or eliminate a multivalued column. Do not miss anything if you want to become an expert.

What is a multi-value lookup column and what is it for in Access?

Generally, in a database, a single value is entered for each cell. This allows maintaining a certain order to view, relate and consult the information, among other things. Since version 2007, Access includes the possibility of having a multi-value field. Namely, in these fields or columns it is possible to integrate more than one data at a time. All this is done with the purpose of being able to select later which aspects of that data will be taken into account to work on.

Once a multi-valued lookup field is created, it usually will be displayed as a dropdown. In this way, it will be possible to open it as many times as necessary and choose the options by checking the corresponding boxes. This is widely used in companies that keep track of employees and need to assign different resources to each person. In this example, when formulating a multivalued column You can choose who will work on each project or who will be assigned a resource each time.

It is only necesary activate or deactivate them according to the convenience of the work. Then, each alternative that is chosen will be seen in the field as the same information separated by commas. If you are already familiar with the many-to-many relationship between tables, it will be easier for you to understand how it works. Therefore, it is important understand how to create such fieldssince these will not appear automatically in your database.

What are the benefits of using a multivalued lookup field in Access?

The most important thing about using a multivalued field is having multiple related data from one placeThis in itself is a benefit. However, it is not the only advantage that these types of fields have. Among other things, it offers a perfect structure for designing databases that can then be shared on Windows SharePoint Services.

This tool windows uses a type of multivalued lookup fields. So whether you’re going to move a table or intend to link to one of its lists, compatibility will be perfect. Also, if you need to introduce a selectable list of a small size in your database, a multi-valued field will be the best solution. Above all, because you will be able to use this function without having advanced programming knowledge.

You will also have a small relationship between data without having to build a more complex database. This means that you will have the search assistant that will guide you through the entire process without complications so that the creation and use of a multivalued column becomes a very simple task.

Learn step by step how to create or delete a multivalue lookup column in Access

Below, you can find a complete guide that you must perform to create or delete a search column in Microsoft Access:

• The first thing you’ll need to do is create a multi-value column or field. For it open the Microsoft Access program and the database what do you need. Later, double-click the Tables drop-down list to open the tool in which you you will generate the multivalued column.
• automatically A function will open, called Datasheet View.
• Go to the tab Data sheet, and then click on the option fields and columns
• Once there, click on the Search Column button.
• This will open a dialog box, which is the Search assistant. This assistant, among other things, is the one that allows the creation of multi-value fields.
• Within the box you will see two options I want the lookup column to look up the values ​​in a table or query Y I’ll write the values ​​you want. Choose this last option to modify a column.

Another way to access the lookup wizard is to open the table in Design View. Then you will have to right click on the specific field and choose the function Type of data. In each field a drop-down list will appear in which you must choose Search assistant.

To shape your search column according to values ​​that you indicate, you must follow these steps:

• Select the second option, Base the column on a list of values ​​and then click I’ll write the values ​​you want.
• Click on Following. Then, you will have to write the number of columns that you are going to form in a small box.
• Now you will see something similar to a empty micro table, ready to fill. In this tool you will also be able to modify the borders to enlarge the size of the cells and have a better visualization.
• Type each value you want to integrate into the cells. These can be names, features, vendors, or any other variable you usually work with.
• For move from one column or row to the other press tab (TAB).
• Once you finish the process you will have to click on Following.
• You will then see a search box. In this function you must indicate the label and check the box with Yes in Allow multiple values.
• When you have finished shaping your multivalue field, you will have to select the button To finalize and then click Save in the corresponding table.

Your field will be displayed as a dropdown list from the Datasheet View where you will be able to choose the values ​​you want from there. You should keep in mind that when you choose multiple columns you have to indicate which column will be used to identify each value.

In addition, you can also create the multivalued field based on a query or on a table. For this, you must select the option I want the lookup column to look up the values ​​in a table or query. This will take you to choose the table or consult the fields you need.

In case you need remove this type of fields pay attention to the following process:

• Since Microsoft Access opens the table where the multivalued lookup field or column is located in the list Tables. To do this you will have to right click.
• Enter the drop-down menu and select the option Design View.
• This will take you to where you can see the typical layout grid. Next, click on the multivalue field you want to delete and choose Delete field. You will also be able to press the key del.
• Choose Yes when a warning box appears. Remember that this option is final and may not be used for any object within the database
• Guard the changes in the table.

If you want to know if any important object in the table depends on the multivalued field you want to deleteyou can check it in a very easy way.

To do this, you will have to follow this guide:

• Go to the tab Tools of databases.
• Then tap on Relations.
• Then select the function object dependencies and you will be able to know which objects have a dependency on the multivalued field.