Microsoft Access It allows create a multi-value lookup column. This will help you to display different items at the same time by means of a drop down 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 you have to use a multivalued lookup field.
You will find this information in the following paragraphs, as well as you will also be able to know the step by step that you must do to create or delete a multivalued column. Don't miss out on anything if you want to become an expert.
What is a multi-valued lookup column and what is it for in Access?
In general, 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 multivalued field. That is to say, in these fields or columns it is possible to integrate more than one data at the same 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.
Once a multivalued lookup field has been created usually will show as a drop down list. In this way, it can be opened 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 turn them on or off according to the convenience of the job. 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 fields, as 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 to have multiple related data from one place, this in itself is a benefit. However, it is not the only advantage these types of fields have. Among other things, it offers a perfect structure to design databases that can then be shared in Windows SharePoint Services.
This tool Windows uses a type of multivalued lookup fields. So whether you're moving a table or intending to link to one of its lists, the compatibility will be perfect. Also, if you need to enter a selectable list of a small size in your database, a multivalued field will be the best solution. Above all, because you will be able to use this function without having advanced knowledge in programming.
You will also have a small relationship between data without having to form 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 multivalued lookup column in Access
Below, you can find a complete guide that you must follow to create or delete a search column in Microsoft Access:
- The first thing you will have to do is create a multivalued column or field. For it open the Microsoft Access program and the database what do you need. Then, double-click the Tables drop-down list to open the tool in which you will generate the multivalued column.
- Automatically a function will open, called Datasheet View.
- Go to the tab Data sheet, and then click 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 wizard, among other things, is the one that allows the creation of multivalued fields.
- Two options will appear inside the box I want the lookup column to look up the values in a table or query and I'll write the values you want. Choose this last option to modify a column.
Another way to access the search 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 are going to have to write the number of columns that you are going to form in a small box.
- Now you will see something like a micro table empty, ready to fill. In this tool you will also be able to modify the borders to expand the size of the cells and have a better visualization.
- Write each value that you want to integrate inside the cells. These can be names, characteristics, vendors, or any other variable that you usually work with.
- For move from one column or row to the other press the 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 mark the box with Yes in Allow multiple values.
- When you have finished shaping your multivalued field, you will have to select the button Finalize and then click save in the corresponding table.
Your field will be displayed as a drop-down list from the Datasheet view where you will be able to choose the values you want from there. You must bear in mind that when you choose several columns you have to indicate which column will be used to identify each value.
Besides, you can also create multivalued field based on query or 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 allow you to choose the table or check 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 Tables list. For this you will have to right click.
- Enter the drop-down menu and select the option Design view.
- This will take you to see the typical layout grid. Next, click on the multivalued field you want to delete and choose Delete field. You will also be able to press the key Delete.
- Choose Yes when a warning box appears. Remember that this option is final and it cannot be used for any object within the database
- Guard changes in the table.
If you want to know if any important object in the table depends on the multivalued field you want to delete, you 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 click Relations.
- Then select the function Object dependencies and you will be able to know which objects are dependent on the multivalued field.