Webucator blogs about online learning and training.

With so much collaboration in today’s business world, we often assign two or more people to the same project. In previous versions of Access, you had to enter separate records for each person to that project. Not anymore! Access has now included a multivalue field in the database. Is it hard to set up? Not at all. For example, in your database you already have a table listing the employees. You will want to create a separate table for your projects. In this projects table, you will have the fields of Project Id, Project Name, Description, Due Date, and Employees.


Open your table Design View and select the Employees field. For the data type, click on the drop down arrow and choose Lookup Wizard.

  1. It is now a simple matter of following the steps of the wizard. This is the opening screen. You can choose to either look up the information that you want in another table in the same database or you can choose to type in your own list. Click the Next button.
  2. In the second Screen, you simply choose the table you wish to use. Then click the Next button.
  3. The third screen allows you to choose the fields that you want to see in the drop-down.
  4. The fourth screen allows you to sort your list by whatever field you choose. You can sort by either Ascending or Descending order.
  5. In this screen you can see how your drop-down list will look to your users. If needed, you can also resize the column width by dragging the right edge of the column border.
  6. This is the final screen and be sure to put the checkmark in Allow Multiple Values.

You’re done. Now when you switch back to datasheet view of your table and you tab into the Employees field, you will see the drop-down arrow to click. Your list of choices will be displayed.

Simply click on the names of the employees that are working on this project and then click okay.


Finish adding your information to your record and when you have moved to another record, the datasheet will look like this.

Now both names of your participating employees are included in this one record.

To hear about the latest Office 2010 news, blogs, and training, subscribe to our newsletter. Click here to subscribe.

4 Responses to “Creating a Multi-value field in Access 2010”

  1. Great help but now what I have a question. Imagine this is a school and each student has multiple classes. Now the teacher wants an attendance list with the student in their class listed once. How can this be done using reports and a query? Thanks

  2. When I do this and look at my records in a form view, it is as if a record has been duplicated for each multivalue and I have to click through the duplicated records to get to the next new record with different details. However when I go into the main table the record only shows once??!!

  3. this is a nice enhancement to excel, however problems arise if you use a multi-value field in a pivot chart. The data element names were truncated to 2 characters on one machine, and to 4 characters on another. Something is unstable.

  4. hi Dan,
    run querie, add ONLY Student name

    then add Class.Valued

    then add code [] under Class.Valued

    hope this helps

Tweetbacks

© Webucator, Inc. All rights reserved. | Toll Free: 877-932-8228 | Outside the USA: 315-849-2724 | Fax: 315-849-2723