Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Lookup fields allow the database user to choose data from a list instead of typing in their own fields. This both speeds up data entry and reduces errors. When using a Lookup field, you can either reference data in another table or type in your own list. So let's create our fourth table, one to take our orders. Click on the Create tab and then click on Table Design. First, we need an OrderID, essentially an invoice number. Let's use the AutoNumber Data Type.
This will also be our unique identifier. So click on the Primary Key button. Next, we need the date of our order. Enter in "OrderDate" and choose Date/Time for the Field Type. Next, we need to know who was placing the order. We'll do a Lookup field to reference the customer's ID, and as an added bonus use the company name to help us know who was who. So enter in Customer and, from the Data Type list, choose Lookup Wizard.
In the dialog box, leave it on the default so that we can get the data from another table and click Next. The Table we'll choose is Customers. So click Next. Now, which fields from the Customers Table contain the values we want included in the Lookup field? So we want to use the CustID as a reference, and also the Company name, and click Next. We'll want our options list sorted by Company. So pick that from the list and then leave it on ascending.
In the next window, it will show us how our dropdown will look. But because we have customers with more than one branch, we'll turn off Hide key column so that we can see both the ID number and the Company name. Click Next. On this screen, we have the choice of which of the two fields we want to store. We're going to go with CustID. Click Next. If you'd like, you can change the Field Name to a label and make it say anything you would like it to say. We are going to leave it with Customer. We are going to Enable Data Integrity and tell it to Cascade Delete.
That way if we delete a customer from our database, it will also delete their associated orders. Go ahead and click Finish. It says, The table must be saved before relationships can be created. Go ahead and Save now? We'll say Yes. And our Table Name is going to be Orders. Click OK. Okay, so now let's finished off our orders table. Click back under the next Field Name and enter in Quantity, and this is going to be a Number. Don't forget that you can just type the letter and it will auto fill.
Last is OrderStatus, again with no spaces in between. This is going to be another Lookup field. So we'll start the same way, selecting the Lookup Wizard, but this time we're going to type in the values that we want. So click that radio button and click Next. Here is where we enter in our choices. I'll click in the box, and our OrderStatus has three options. The first is that the order has been processed, the second is that it's been backordered, and our third, OrderStatus is complete.
Then click Next again. If I want to change the label, I could. We're going to leave it with OrderStatus. Now if we want the user to only be able to use our three choices, we want them to limit it to the list. So we'll put a check mark in this box. Otherwise, the user can also enter in additional values. We don't want to allow multiple values, because the order can only have one status. So I'll click Finish. Again, save the table. Now if you get this error message, just go ahead and click Yes.
By using Lookup fields, you can pull data from other tables or restrict the choices the user can enter into a field.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74776 Viewers
80 Video lessons · 129857 Viewers
52 Video lessons · 64061 Viewers
59 Video lessons · 49851 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.