We will now open a pre-existing Access database table, so that we can explore the interface and discuss key terminology. Our Access database contains a table to keep track of faculty at the fictional University of the Midwest.
Step1. Launch Access.
Step2. In the pane on the left,
Click Open Other Files
Step3. Navigate to the epclass folder on the desktop.
Step4. To open the Access-The Basics folder,
Double-Click Access-The Basics
Step5. To open the correct file,
Step6. To dismiss the security warning, in the yellow bar at the top of the document window,
Step7. To make this file a trusted document, if necessary,
Exploring the Interface
The Access interface is very similar to other Microsoft Office applications. There are two main areas that we will be working with: the navigation pane and the object workspace:
The navigation pane will contain all of the different objects in a database (i.e., queries, forms, tables, reports, etc.). We can organize the objects in our database into categories and groups. Categories let you arrange the objects in the navigation pane, and groups can be filtered. The search field can be used to find database objects quickly. The object workspace is where most editing and data entry will take place. Each open database object will have a separate tab in this section.
In the next section, we'll open tblFaculty and learn more about the characteristics of the fields it contains.
Exploring a Table
In our file, there is only one object: the faculty table. We'll open it and explore some of the properties of a table object in Access.
Step1. To open the faculty table, in the navigation pane,
Step2. To minimize the navigation pane,
Step3. To open the Fields command tab, in the Table Tools section of the Ribbon,
|Office||Rooms on campus all have a 5- or 6-character abbreviation: two letters for the building and 3-4 characters for the room number|
|Usernames cannot be longer than 8 characters|
|DeptCode||All departments have a 4-letter abbreviation|
|Rank||Ranks for faculty are Assistant Professor, Associate Professor, and (Full) Professor|
Step4. To make the LastName field active, in the LastName column,
Click the first row of data
Step5. To move to the Phone field, press:
Tab key 3 times
NOTE: While it is possible to store the phone number as 10 characters and just have Access show it in the table with the additional characters, storing the phone number with room for the extra characters makes it possible to export data with the phone number format, instead of just a string of 10 digits.
Step6. To move to the DeptCode field, press:
Tab key twice
Step7. To move to the Salary field, press:
Tab key twice
Step8. To move to the HireDate field, press:
Step9. To move to the Active field, press:
Tab key twice
Step10. To move to the Bio field, press:
Identifying the Primary Key
As we've seen, the primary key for a table should be a field that is unique for every record. Looking at tblFaculty, one obvious candidate for primary key seems to be the EmployeeID field. This number should be unique for every faculty member. However, in datasheet view, it's hard to tell if this field, or any other, has already been established as the primary key.
There are some clues, however, that suggest that EmployeeID has been set up as the primary key for this table. Let's examine that field again.
Step1. To move to the EmployeeID field, press:
Step2. To switch to design view, on the left side of the Ribbon,
Step3. To select the EmployeeID field, if necessary, in the list of fields,
NOTE: You can change a primary key by selecting the desired field in design view, and then clicking the primary key button.
Step4. To return to datasheet view,
Adding a New Record
The University of the Midwest has just hired a new faculty member, so we're going to add her information to tblFaculty.
Step1. To begin adding a new record, at the bottom of the table's entries,
Click in the first cell of the first empty row
Step2. To add the employee ID, in the EmployeeID field, type:
0000211027 Tab key
Step3. To add the last name, in the LastName field, type:
Alfaro Tab key
Step4. To add the first name, in the FirstName field, type:
Bernice Tab key
Step5. To add the office location, in the Office field, type:
AH201 Tab key
Step6. To add the phone number, in the Phone field, type:
8127761403 Tab key
Step7. To add the username, in the Email field, type:
balfaro Tab key
Step8. To add the department code, in the DeptCode field, type:
math Tab key
Step9. To add the rank, in the Rank field, type:
Assistant Professor Tab key
Step10. To add the salary, in the Salary field, type:
60000 Tab key
Step11. To select the date picker,
Step12. To select today's date,
Step13. To move to the RetirementDate field, press:
Step14. To move to the next field, press:
Step15. To set the Active field to Yes, press:
NOTE: You can also check the checkbox using the mouse.
Step16. To move to the Bio field, press:
Step17. To fill in some placeholder text for the Bio field, type:
Bio goes here
Step18. To save our data entry,
Click in a cell in another record
NOTE: The save function in Access is used to save design changes to the database itself, not to save data that has been entered. We will use the save function later in the workshop.
Notice that by default, tables in Access are displayed in primary key order. Thus, our faculty list is sorted by employee ID. However, our newest faculty member's record is still at the bottom of the list. This allows us to enter a series of new records, all of which remain at the bottom of the table until we close it. The next time Access opens the table, the entries will be reordered by primary key.
Let's close our table and reopen it to see the new order. Then, we'll show how to sort our data by other fields.
Step1. To close our table, in the upper-right corner of the object workspace,
NOTE: Be careful not to click the Close button for the entire database, which can be found in the far upper-right corner of the Access window. If you accidentally closed Access, simply reopen it and choose the UofMW.accdb file from the Recent Files menu.
Step2. To reopen the navigation pane, in the upper-left corner of the workspace,
Step3. To reopen the table,
Step4. To sort the faculty table by the LastName field, in the field headers,
Step5. To sort the records A to Z,
Click Sort A to Z
Step6. To make the HireDate field visible, if necessary,
Step7. To sort the records by hire date, in the HireDate field,
Step8. To sort the records with the newest hire on top, from the drop-down menu,
Click Sort Newest to Oldest
Step9. To begin closing tblFaculty,
Step10. To finish closing tblFaculty, in the drop-down menu,
Step11. To close without saving the new order of records,