One table or multiple tables?

Very often, people start using Microsoft Access with no training and because most users are familiar with Microsoft Word and Microsoft Excel, they try to put everything into one table. If you recall in Lesson 1, the sample database showed multiple tables.

The reason for multiple tables is simple. You want to break your data into the smallest bits possible. Therefore, look at any data that could be repeated (for example, a list you can use over and over) or mistakenly entered (for example, data that someone may not enter correctly) and create separate tables for that data.

This may sound complex, but it's really not. Although databases are extremely useful, they need to be set up correctly to minimize data entry and facilitate accurate reports. A good example is the state name. A human can identify different state names as being the same even when they're represented differently, such as:

However, to a computer, those are three different states! One way you can minimize errors is to limit the state entry to two characters. Now you have a different problem. Do you know the state abbreviation for Maryland? Is it MA, MY, ML, or MD?

If you said MD, you're correct, but do you want to gamble with your data? A simple way to eliminate problems like these is to create a list of all the states and their abbreviations. Then you can create a lookup field that pulls the information from another table. This way, your data entry person can be sure of the correct information and your reports are that much more accurate. You create a state table and a lookup field later in this lesson.

Set up your first table

To set up your first table, start Access and at the Startup window, choose the database that you created in the Lesson 2: AccessIntro.mdb. You can also open the database by going to the location of the file and double-clicking it.

After you open this file, you see the main database window. Verify that you're on the Tables section by clicking Tables to the left, and then double-click Create table in Design view.

Figure 3-1: Main database window.

You should now see the Design view of a table, as shown in Figure 3-2.

Figure 3-2: Blank table design window.

Let's review the fields we determined in Lesson 2. You should've finished the list and the types for the list, as shown in the following table.

Enter your field names and types

Now it's time to get your hands dirty and enter your field names and types. Your cursor should be located in the first Field Name location. Simply type in your first Field Name, and then tab to the next field and enter the type. It will default to Text, so in 90 percent of the cases, this is correct. In our first Field Name, LeadID, the type is AutoNumber, so simply click the drop-down arrow to see the complete list of possible types and choose from that list or simply type A and AutoNumber appears.

After choosing a type, tab to the next field. This field is for descriptions if the information is unclear. Figure 3-3 shows detailed descriptions, which you can enter if you want. They're there to help you remember things about the field when you go back to modify it later. It's a completely optional field.

You'll also set the first field, LeadID, as your Key Field. To do so, just click the Key icon in the toolbar while the cursor is anywhere in the LeadID field. You'll see a Key icon appear to the left of the field.

Tab through and enter each field name you see in the table. Notice that LeadStatus is set as Text. We will be going back and changing that field and a few others later in the lesson. For now, when in doubt, use Text.

Set field properties

Now, that you're familiar with the FieldName and Data Type fields, as well as Key Fields, it's time to focus on size. As you continue to enter fields, you'll notice the bottom of the screen changes depending on the Data Type you've chosen. This is a list of Field Properties that may be set depending on the Data Type you've chosen:

In Figure 3-3, you can see that the arrow to the left shows the State field is currently selected. Below the list of fields, you see Field Properties. As you click each FieldName, you can see the properties for that field. They're different depending on the Data Type. The following details the options you have if the Data Type is set to Text:

Figure 3-3: Text properties.

Now you finish and close the table. Set the properties as indicated in the table. When you're finished, you can click close and make sure to click Yes when it asks you to save. You can also save as you go by clicking the save button on the Standard toolbar or choosing File > Save As.

When it asks for a name for the table, be sure to name it tblLeads so you know what it is when you return to it in the next section.

Set up your second table and data entry

In this section, you set up a table, tblStates, for a list of states, and then enter data into the table for each of the states. Start the same way you did for the last table. If you closed tblLeads, you'll see the main database window again. Click Create table in Design view to once again see the empty table window.

This is a quick and easy design phase because there are only two fields: StateID and State. StateID is the first field and is the Key Field. It's the list of two-character state abbreviations, so make it a text field and make the field size equal to two characters, as shown in Figure 3-4.

Figure 3-4: tblStates.

After you enter these two fields, click the Table View button, as shown in Figure 3-5. When you click this button, you get a drop-down menu that provides you with two ways you can view a table: either in Datasheet View, which is where you enter data, or in Design View, which is how you created your two tables. Choose Datasheet View to enter the data.

Figure 3-5: Design View and Datasheet View menu.

Again, you get a message asking you to save the table before continuing and you can say Yes, and save it as tblStates.

Now, you can begin data entry directly into the table. Simply start with the first code, AK, then tab to the next field and enter the state name, Alaska, and tab to the next code, AL, and then tab to enter Alabama and so on.

For a complete list of state abbreviations, visit the United States Postal Service Web site at www.usps.com/ncsc/lookups/abbreviations.html#states

Notice a few things about the Datasheet view as you enter your states data. As you can see in Figure 3-6 and as you edit data, the active record (AL/Alabama in this case), shows a small icon that looks like a pencil to the left. As you edit data in each record, this pencil icon indicates that an edit is in progress. If you make a mistake as you edit, you can press the Esc key on your keyboard to abort the edit and return the data to its previous condition.

Figure 3-6: Datasheet view.

TIP
Data is saved as soon as you move off the record. You don't need to click Save for data, only for changes to database structure.

Look at the bottom of your Datasheet view to see the total number of records and which record you're on. Figure 3-6 shows Record 2 of 56. As you move through the records, those numbers change.

To move quickly through the records, use the scroll arrows and the scroll arrows with lines to move all the way to the first record or the last record. Practice this skill.

Continue entering all the state data. Figure 3-6 has 56 states because it includes BC/British Columbia, DC/District of Columbia, ON/Ontario, OT/Other-NonUS, PR/Puerto Rico, and VI/Virgin Islands. You may choose to only include states you work within. Record numbers reflect the actual number of records in the table.

Sorting and filtering

You can enter data in any order and then sort it later. At the top of the Datasheet view window, you'll see some icons, as shown in Figure 3-7. Each view has some tools specific to the view you're looking at.

Figure 3-7: Data entry tools.

From left to right, the tools are Sort Ascending, Sort Descending, Filter By Selection, Filter by Form, Filter On/Off, Find, New Record, and Delete Record. Try all these until you understand how they work.

Link tables

In this last section for this lesson, you link the two tables you created, so that data entry staff can choose from a drop-down box from only the list of states that you entered.

Open the tblLeads in Design view, and click the field StateCode. Now, tab to the Data Type column and choose Lookup Wizard from the drop-down menu.

Figure 3-8: Lookup Wizard.

Wizards are a step-by-step process, just read each screen, answer accordingly, and then click Next. You can always click Back if you've made an error, and you can always click Cancel to exit the wizard without making any changes. In this wizard, the first screen asks where the choices should come from. You can type in choices if there will only be making a few, but one of the advantages to creating a separate table like we did with the state table is that you can later create a form for people to use to make changes to the database. You'd do this because you probably don't many people accessing the Design View of your database because they may not know how to use it.

So, for this choice, we leave the default choice of wanting to look up the values in a table or query, and click Next.

Now, click your state table, tblStates, and then click Next.

Figure 3-9 shows where you choose your fields to include in the lookup field. If you click the top arrow in the middle, one field at a time will go to the right side. If you click the double arrow, both fields will go to the right. You can choose as few or as many as you like. In this example, we choose all the fields in the table, but you're not required to.

Figure 3-9: Lookup Wizard selected fields.

Make sure both fields are on the right (selected fields side), and then click Next.

In the next window, you see a list of the states, but you won't see the state IDs (AK, AL, AR, and so on). You also see a checkbox for Hide Key Column (recommended). Because in most cases, the Key Field is a number (remember the AutoNumber field you set as Key field in the tblLeads?), Access assumes you want to hide that field. In this case, you may want to or not. It's not important which you choose. Try it both ways. I choose to hide the field, but you may choose not to. If you decide you don't like your choice, you can always go back through the wizard to change it. There are also manual ways to change your choices, but they're more advanced.

The last wizard screen asks you for a label for your field. This is the same as the caption field. Type State, and click Finish.

You should receive the message: The table must be saved before relationships can be created. Save now? Click Yes, and then click the Datasheet View button to see the data in the table.

If you now scroll across to the right, you'll see your State field. When you click in the field, you'll see a drop-down arrow next to it. If you type a few letters in, the field fills with information. You can enter data this way by hitting tab to move to the next field when you see the correct state or you can click the drop-down arrow to select any of the states, as shown in Figure 3-10.

Figure 3-10: State lookup field.

In Figure 3-10, notice that because we hid the State ID, you can only see the state names. This is simply a matter of preference. If you chose not to hide the key field, you'll see the abbreviations and the state names.

This is a simple automation process to make your data more accurate while also easing data entry.

Moving on

In this lesson, you created two tables and linked them. During this process, you learned about field types and properties. You can continue to build tables in this manner to add functionality to your database.

In Lesson 4, you'll create forms for easier data entry.

Assignment: Create Tables

If you haven't yet done so, complete the tables, tblLeads and tblStates, set up in Lesson 3.

After you've done that, create a new Access Database, and then create two new tables. This time, use the Create table by entering data method and the Create table by using wizard method as opposed to the Create table in Design view method that we used in Lesson 3.

Question 1
True or False: A table is made up of records and records are made up of fields.

A) True
B) False

Question 2
True or False: A record number is the same as the autonumber.

A) True
B) False

Question 3
Which of the following are included in the field properties? (Check all that apply.)

A) Size
B) Format
C) Data
D) Caption

Question 4
True or False: When using a table as a lookup source, you must use all the fields in that table.

A) True
B) False

Question 5
True or False: Once you make choices and complete a wizard, you cannot go back and change your choices.

A) True
B) False