- A lookup field allows you to select a value from a list when populating a table with information. Like validation rules and default values, this is another tool that you can use to ensure that the information in your Access database is valid and consistently entered. Now you notice here inside of the LandonHotel-Lookup:Database, I've imported a couple of more tables that will support the hotel's operations. One of them, is the LandonEmployees table. If we double click on it, we can take a look at it. Inside of it, I've got some information about the names of the members of our executive team, as well as their titles.
I've got address, the country where they're based, their phone number, email and a long bio right over here. Now the way the hotel has properties in several different countries, so when it comes time to adding a new employee to the database, I want to make sure that they get assigned to an appropriate location. We can do that by creating a Lookup field on the Country here. So we'll switch into design view in order to add it. I'm going to come down here and select the Country field, and right now the data type is listed as Short Text. If I click here, I'll get the drop-down list where we can change it. And from the menu, I'm going to choose Lookup Wizard at the very bottom.
That will start up the Lookup Wizard, where we have a couple of options. The first option is to have the Lookup field values come from an existing table or query. So if we had a listing or another table over here of the different countries, we could use that. I'm going to choose this option here, to type in the values that I want. In which case, when I press Next, I'll just fill in this little table here with the appropriate values. So the first one, is going to be United Kingdom. The next one, is going to be United States, followed by India and Ecuador. After typing in those values, I'll press the Next button here.
And we get to label our new field, in which case, it's just going to pull out the name of the original field which is Country, so I'll just leave it at that. There's a checkbox right here that says Limit to List. If I leave this unchecked, Access will let me enter in whatever I want in the list, even though it will give me some options with the drop-down menu. If I turn on Limit to List though, I can only choose from the options that are available in the drop-down list. I won't be able to enter in anything else. So in this case, I'm going to turn on Limit to List, to make sure that I only select from those four options that we just typed in. And we'll go ahead and press the Finish button here.
Now at first it might look like nothing has changed. My data type, you'll see, is still listed as Short Text. And that's because the value that we're storing here, in fact, is the Short Text data type. What did change though is down here, in the Field Properties, you'll notice we have two tabs. One of them is General and then we have Lookup. When we switch over to the Lookup tab, you'll see some information here. The Display Control is now listed as a Combo Box. The Row Source Type is a Value List. And then the Row Source items are the listed items that we typed in a moment ago. So we see United Kingdom wrapped in quotation marks and then a semi-colon.
Then United States in quotes with a semi-colon. And India and Ecuador also wrapped in quotation marks. And all of those are separated by semi-colons. So that's the listing that's going to appear in our drop-down menu here. And then we have some additional information that control how the list works. So at this point, we can go ahead and close and save our table. I'll switch to data sheet view. And I'll accept this button here to save the changes. And now if I go over here to one of our Country fields, let me just double click here to expand that column a little bit. When I click on the first country, you notice I get a drop-down menu now.
And if I open that up, I can make a selection from these four items right here. So I can switch it to India or I can switch it to United States. If I type in another value, perhaps I'll type in China and then when I press Tab to move out of that field, I'll get this warning message here saying that the text you entered isn't an item in the list. And that's because we checked on that option to limit the valid entries to the ones on the list. Let's go ahead and say OK to this and we'll have to make a change here. I'll just change it back to United Kingdom. In which case, that does become a valid entry so I can move out of that cell. So in addition to providing control over what gets entered, Lookup lists can dramatically speed up data entry as well.
When choosing from a list of acceptable values, you can simply type in the first few characters to select the matching option.
Author
Released
9/22/2015The course also shows you how to build queries and action queries, create and design forms, use macros, integrate Access with the rest of the Office 365 suite, and maintain your databases over time.
- Creating a new database
- Creating tables and new data types
- Importing and entering data
- Setting up relationships and primary keys
- Adding validation rules
- Sorting and filtering table data
- Building queries
- Designing forms
- Creating reports
- Attaching macros to buttons and tables
- Working with Excel and Outlook data
- Maintaining an Access database
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2016 Essential Training
with Dennis Taylor8h 53m Beginner -
Office 2016 New Features
with David Rivers47m 33s Intermediate -
Outlook 2016 Essential Training
with Jess Stratton2h 49m Beginner
-
Introduction
-
Welcome1m 25s
-
-
1. Getting Started
-
Key database concepts4m 14s
-
Using the Navigation pane4m 47s
-
Using the Tell Me feature1m 47s
-
Making backups2m 14s
-
Accessing Access help2m 15s
-
-
2. Creating Tables
-
Entering data4m 21s
-
Importing tables5m 37s
-
3. Setting Field Properties
-
Setting the default value2m 35s
-
Creating lookup fields3m 51s
-
Solution: Creating tables5m 50s
-
4. Organizing Records
-
Formatting columns3m 55s
-
Sorting table data3m 48s
-
Filtering table data3m 22s
-
-
5. Using Queries
-
Building expressions7m 26s
-
Solution: Creating queries4m 38s
-
6. Working with Specialty Queries
-
Using update queries6m 44s
-
Using make table queries3m 33s
-
Using delete queries4m 13s
-
Using append queries3m 50s
-
-
7. Creating Forms
-
Generating forms from tables4m 37s
-
Using the Form Wizard4m 35s
-
8. Designing Forms
-
Setting data sources6m 8s
-
Understanding input boxes5m 24s
-
Record navigation4m 2s
-
Adding buttons4m 21s
-
Assigning a tab order4m 36s
-
Creating a navigation form4m 25s
-
Solution: Creating forms4m 29s
-
9. Creating Reports
-
Introducing reports4m 19s
-
Using the Report Wizard4m 53s
-
Creating calculated totals3m 52s
-
Creating labels5m 21s
-
Adjusting print settings4m 6s
-
Solution: Creating reports5m 29s
-
-
10. Working with Macros
-
Creating macros6m 9s
-
Launching macros at startup2m 36s
-
-
11. Integrating Access with the Office Suite
-
Exporting tables to Excel3m 17s
-
Emailing with macros4m 13s
-
-
12. Maintaining the Database
-
Documenting your work4m 11s
-
Creating custom Ribbon tabs2m 57s
-
Conclusion
-
Next steps1m 2s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Creating lookup fields