Homework answers / question archive / New Perspectives Access 2019 Module 1 Textbook Project AC 1-3Starting Access and Creating a Database Now that you’ve learned some database terms and concepts, you’re ready to start Access and create the Lakewood database for Donna

New Perspectives Access 2019 Module 1 Textbook Project AC 1-3Starting Access and Creating a Database Now that you’ve learned some database terms and concepts, you’re ready to start Access and create the Lakewood database for Donna

MS Access

Share With

New Perspectives Access 2019 Module 1 Textbook Project

AC 1-3Starting Access and Creating a Database

Now that you’ve learned some database terms and concepts, you’re ready to start Access and create the Lakewood database for Donna.

To start Access:

On the Windows taskbar, click the Start button . The Start menu opens.

On the Start menu, scroll down the list of apps, and then click Access. Access starts and displays the Recent screen in Backstage view. See Figure 1-4.

Figure 1-4Recent screen in Backstage view

When you start Access, the first screen that appears is Backstage view, which is the starting place for your work in Access. Backstage view contains commands that allow you to manage Access files and options. The Recent screen in Backstage view provides options for you to create a new database or open an existing database. To create a new database that does not contain any data or objects, you use the Blank database option. If the database you need to create contains objects that match those found in common databases, such as databases that store data about contacts or tasks, you can use one of the templates provided with Access. A template is a predesigned database that includes professionally designed tables, reports, and other database objects that can make it quick and easy for you to create a database. You can also search for a template online using the Search for online templates box.

In this case, the templates provided do not match Donna’s needs for the clinic’s database, so you need to create a new, blank database from scratch.

To create the new Lakewood database:

Make sure you have the Access starting Data Files on your computer.

Trouble? If you don’t have the starting Data Files, you need to get them before you can proceed. Your instructor will either give you the Data Files or ask you to obtain them from a specified location (such as a network drive). If you have any questions about the Data Files, see your instructor or technical support person for assistance.

On the Recent screen, click Blank database (see Figure 1-4). The Blank database screen opens.

In the File Name box, type Lakewood to replace the selected database name provided by Access, Database1. Next you need to specify the location for the file.

Click the Browse button to the right of the File Name box. The File New Database dialog box opens.

Navigate to the drive and folder where you are storing your files, as specified by your instructor.

Make sure the Save as type box displays “Microsoft Access 2007–2016 Databases.”

Trouble? If your computer is set up to show file name extensions, you will see the Access file name extension “.accdb” in the File name box.

Click OK. You return to the Blank database screen, and the File Name box now shows the name Lakewood.accdb. The filename extension “.accdb” identifies the file as an Access 2007–2016 database.

Click Create. Access creates the new database, saves it to the specified location, and then opens an empty table named Table1.

Trouble? If you see only ribbon tab names and no buttons, click the Home tab to expand the ribbon, and then in the lower-right corner of the ribbon, click the Pin this pane button to pin the ribbon.

Refer back to the Session 1.1 Visual Overview and spend some time becoming familiar with the components of the Access window.

AC 1-3aWorking in Touch Mode

If you are working on a touch device, such as a tablet, you can switch to Touch Mode in Access to make it easier for you to tap buttons on the ribbon and perform other touch actions. Your screens will not match those shown in the book exactly, but this will not cause any problems.

Note: The following steps assume that you are using a mouse. If you are instead using a touch device, please read these steps but don’t complete them, so that you remain working in Touch Mode.

To switch to Touch Mode:

On the Quick Access Toolbar, click the Customize Quick Access Toolbar button . A menu opens listing buttons you can add to the Quick Access Toolbar as well as other options for customizing the toolbar.

Trouble? If the Touch/Mouse Mode command on the menu has a checkmark next to it, press ESC to close the menu, and then skip to Step 3.

Click Touch/Mouse Mode. The Quick Access Toolbar now contains the Touch/Mouse Mode button , which you can use to switch between Mouse Mode, the default display, and Touch Mode.

On the Quick Access Toolbar, click the Touch/Mouse Mode button . A menu opens with two commands: Mouse, which shows the ribbon in the standard display and is optimized for use with the mouse; and Touch, which provides more space between the buttons and commands on the ribbon and is optimized for use with touch devices. The icon next to Mouse is shaded to indicate that it is selected.

Trouble? If the icon next to Touch is shaded red, press ESC to close the menu and skip to Step 5.

Click Touch. The display switches to Touch Mode with more space between the commands and buttons on the ribbon. See Figure 1-5.

Figure 1-5Ribbon displayed in Touch Mode

The figures in this text show the standard Mouse Mode display, and the instructions assume you are using a mouse to click and select options, so you’ll switch back to Mouse Mode.

Trouble? If you are using a touch device and want to remain in Touch Mode, skip Steps 5 and 6.

On the Quick Access Toolbar, click the Touch/Mouse Mode button , and then click Mouse. The ribbon returns to the standard display, as shown in the Session 1.1 Visual Overview.

On the Quick Access Toolbar, click the Customize Quick Access Toolbar button , and then click Touch/Mouse Mode to deselect it. The Touch/Mouse Mode button is removed from the Quick Access Toolbar.

AC 1-4Creating a Table in Datasheet View

Tables contain all the data in a database and are the fundamental objects for your work in Access. You can create a table in Access in different ways, including entering the fields and records for the table directly in Datasheet view.

Reference

Creating a Table in Datasheet View

On the ribbon, click the Create tab.
In the Tables group, click the Table button.
Rename the default ID primary key field and change its data type, if necessary; or accept the default ID field with the AutoNumber data type.
On the Fields tab in the Add & Delete group, click the button for the type of field you want to add to the table (for example, click the Short Text button), and then type the field name; or, in the table datasheet, click the Click to Add column heading, click the type of field you want to add from the list that opens, and then press TAB or ENTER to move to the next column in the datasheet. Repeat this step to add all the necessary fields to the table.
In the first row below the field names, enter the value for each field in the first record, pressing TAB or ENTER to move from one field to the next.
After entering the value for the last field in the first record, press TAB or ENTER to move to the next row, and then enter the values for the next record. Continue this process until you have entered all the records for the table.
On the Quick Access Toolbar, click the Save button, enter a name for the table, and then click OK.

For Lakewood Community Health Services, Donna needs to track information about each patient visit at the clinic. She asks you to create the Visit table according to the plan shown in Figure 1-6.

Figure 1-6

Plan for the Visit table

Field	Purpose
VisitID	Unique number assigned to each visit; will serve as the table’s primary key
PatientID	Unique number assigned to each patient; common field that will be a foreign key to connect to the Patient table
VisitDate	Date on which the patient visited the clinic
Reason	Reason/diagnosis for the patient visit
WalkIn	Whether the patient visit was a walk-in or scheduled appointment

As shown in Donna’s plan, she wants to store data about visits in five fields, including fields to contain the date of each visit, the reason for the visit, and if the visit was a walk-in or scheduled appointment. These are the most important aspects of a visit and, therefore, must be tracked. Also, notice that the VisitID field will be the primary key for the table; each visit at Lakewood Community Health Services is assigned a unique number, so this field is the logical choice for the primary key. Finally, the PatientID field is needed in the Visit table as a foreign key to connect the information about visits to patients. The data about patients and their invoices will be stored in separate tables, which you will create later.

Notice the name of each field in Figure 1-6. You need to name each field, table, and object in an Access database.

ProSkills

Decision Making: Naming Fields in Access Tables

One of the most important tasks in creating a table is deciding what names to specify for the table’s fields. Keep the following guidelines in mind when you assign field names:

A field name can consist of up to 64 characters, including letters, numbers, spaces, and special characters, except for the period (.), exclamation mark (!), grave accent (`), and square brackets ([ ]).
A field name cannot begin with a space.
Capitalize the first letter of each word in a field name that combines multiple words, for example VisitDate.
Use concise field names that are easy to remember and reference and that won’t take up a lot of space in the table datasheet.
Use standard abbreviations, such as Num for Number, Amt for Amount, and Qty for Quantity, and use them consistently throughout the database. For example, if you use Num for Number in one field name, do not use the number sign (#) for Number in another.
Give fields descriptive names so that you can easily identify them when you view or edit records.
Although Access supports the use of spaces in field names (and in other object names), experienced database developers avoid using spaces because they can cause errors when the objects are involved in programming tasks.

By spending time obtaining and analyzing information about the fields in a table, and understanding the rules for naming fields, you can create a well-designed table that will be easy for others to use.

AC 1-4aRenaming the Default Primary Key Field

As noted earlier, Access provides the ID field as the default primary key for a new table you create in Datasheet view. Recall that a primary key is a field, or a collection of fields, whose values uniquely identify each record in a table. However, according to Donna’s plan, the VisitID field should be the primary key for the Visit table. You’ll begin by renaming the default ID field to create the VisitID field.

To rename the ID field to the VisitID field:

Right-click the ID column heading to open the shortcut menu, and then click Rename Field. The column heading ID is selected, so that whatever text you type next will replace it.

Type VisitID and then click the row below the heading. The column heading changes to VisitID, and the insertion point moves to the row below the heading. The insertion point is a flashing cursor that shows where text you type will be inserted. In this case, it is hidden within the selected field value (New). See Figure 1-7.

Figure 1-7ID field renamed to VisitID

Trouble? If you make a mistake while typing the field name, use BACKSPACE to delete characters to the left of the insertion point or use DELETE to delete characters to the right of the insertion point. Then type the correct text. To correct a field name by replacing it entirely, press ESC, and then type the correct text.

Notice that the Table Tools Fields tab is active on the ribbon. This is an example of a contextual tab, which is a tab that appears and provides options for working with a specific object that is selected—in this case, the table you are creating. As you work with other objects in the database, other contextual tabs will appear with commands and options related to each selected object.

Insight

Buttons and Labels on the Ribbon

Depending on the size of the monitor you are using and your screen resolution settings, you might see more or fewer buttons on the ribbon, and you might not see labels next to certain buttons. The screenshots in these modules were created using a screen resolution setting of 1366 x 768 with the program window maximized. If you are using a smaller monitor or a lower screen resolution, some buttons will appear only as icons, with no labels next to them, because there is not enough room on the ribbon to display the labels.

You have renamed the default primary key field, ID, to VisitID. However, the VisitID field still retains the characteristics of the ID field, including its data type. Your next task is to change the data type of this field.

AC 1-4bChanging the Data Type of the Default Primary Key Field

Notice the Formatting group on the Table Tools Fields tab. One of the options available in this group is the Data Type option (see Figure 1-7). Each field in an Access table must be assigned a data type. The data type determines what field values you can enter for the field. In this case, the AutoNumber data type is displayed. Access assigns the AutoNumber data type to the default ID primary key field because the AutoNumber data type automatically inserts a unique number in this field for every record, beginning with the number 1 for the first record, the number 2 for the second record, and so on. Therefore, a field using the AutoNumber data type can serve as the primary key for any table you create.

Visit numbers at Lakewood Community Health Services are specific, four-digit numbers, so the AutoNumber data type is not appropriate for the VisitID field, which is the primary key field in the table you are creating. A better choice is the Short Text data type, which allows field values containing letters, digits, and other characters, and which is appropriate for identifying numbers, such as visit numbers, that are never used in calculations. So, Donna asks you to change the data type for the VisitID field from AutoNumber to Short Text.

To change the data type for the VisitID field:

Make sure that the VisitID column is selected. A column is selected when you click a field value, in which case the background color of the column heading changes to orange (the default color) and the insertion point appears in the field value. You can also click the column heading to select a column, in which case the background color of both the column heading and the field value changes (the default colors are gray and blue, respectively).

On the Table Tools Fields tab, in the Formatting group, click the Data Type arrow, and then click Short Text. The VisitID field is now a Short Text field. See Figure 1-8.

Figure 1-8Short Text data type assigned to the VisitID field

Note the Unique check box in the Field Validation group. This check box is selected because the VisitID field assumed the characteristics of the default primary key field, ID, including the fact that each value in the field must be unique. Because this check box is selected, no two records in the Visit table will be allowed to have the same value in the VisitID field.

With the VisitID field created and established as the primary key, you can now enter the rest of the fields in the Visit table.

AC 1-4cAdding New Fields

When you create a table in Datasheet view, you can use the options in the Add & Delete group on the Table Tools Fields tab to add fields to your table. You can also use the Click to Add column in the table datasheet to add new fields. (See Figure 1-8.) You’ll use both methods to add the four remaining fields to the Visit table. The next field you need to add is the PatientID field. Similar to the VisitID field, the PatientID field will contain numbers that will not be used in calculations, so it should be a Short Text field.

To add the rest of the fields to the Visit table:

On the Table Tools Fields tab, in the Add & Delete group, click the Short Text button. Access adds a new field named “Field1” to the right of the VisitID field. See Figure 1-9.

Figure 1-9New Short Text field added to the table

The text “Field1” is selected, so you can simply type the new field name to replace it.

Type PatientID. Access adds the second field to the table. Next, you’ll add the VisitDate field. Because this field will contain date values, you’ll add a field with the Date/Time data type, which allows field values in a variety of date and time formats.

In the Add & Delete group, click the Date & Time button. Access adds a third field to the table, this time with the Date/Time data type.

Type VisitDate to replace the selected name “Field1.” The fourth field in the Visit table is the Reason field, which will contain brief descriptions of the reason for the visit to the clinic. You’ll add another Short Text field—this time using the Click to Add column.

Click the Click to Add column heading. Access displays a list of available data types for the new field.

Click Short Text in the list. Access adds a fourth field to the table.

Type Reason to replace the highlighted name “Field1,” and then press ENTER. The Click to Add column becomes active and displays the list of field data types.

The fifth and final field in the Visit table is the WalkIn field, which will indicate whether the patient had a scheduled appointment. The Yes/No data type is suitable for this field because it defines fields that store values representing one of two options—true/false, yes/no, or on/off.

Click Yes/No in the list, and then type WalkIn to replace the highlighted name “Field1.”

Trouble? If you pressed TAB or ENTER after typing the WalkIn field name, press ESC to close the Click to Add list.

Click in the row below the VisitID column heading. You have entered all five fields for the Visit table. See Figure 1-10.

Figure 1-10Table with all fields entered

The table contains three Short Text fields (VisitID, PatientID, and Reason), one Date/Time field (VisitDate), and one Yes/No field (WalkIn). You’ll learn more about field data types in the next module.

As noted earlier, Datasheet view shows a table’s contents in rows (records) and columns (fields). Each column is headed by a field name inside a field selector, and each row has a record selector to its left (see Figure 1-10). Clicking a field selector or a record selector selects that entire column or row (respectively), which you then can manipulate. A field selector is also called a column selector, and a record selector is also called a row selector.

AC 1-4dSaving the Visit Table Structure

As you find out later, the records you enter are immediately stored in the database as soon as you enter them; however, the table’s design—the field names and characteristics of the fields themselves, plus any layout changes to the datasheet—are not saved until you save the table. When you save a new table for the first time, you should give it a name that best identifies the information it contains. Like a field name, a table name can contain up to 64 characters, including spaces.

Reference

Saving a Table

Make sure the table you want to save is open.
On the Quick Access Toolbar, click the Save button. The Save As dialog box opens.
In the Table Name box, type the name for the table.
Click OK.

According to Donna’s plan, you need to save the table with the name “Visit.”

To save, name, and close the Visit table:

On the Quick Access Toolbar, click the Save button . The Save As dialog box opens.

With the default name Table1 selected in the Table Name box, type Visit and then click OK. The tab for the table now displays the name “Visit,” and the Visit table design is saved in the Lakewood database.

Click the Close ‘Visit’ button on the object tab (see Figure 1-10 for the location of this button). The Visit table closes, and the main portion of the Access window is now blank because no database object is currently open. The Lakewood database file is still open, as indicated by the filename in the Access window title bar.

AC 1-5Creating a Table in Design View

The Lakewood database also needs a table that will hold all of the invoices generated by each office visit. Donna has decided to call this new table the Billing table. You created the structure for the Visit table in Datasheet view. An alternate method of creating the structure of a table is by using Design view. You will create the new Billing table using Design view.

Creating a table in Design view involves entering the field names and defining the properties for the fields, specifying a primary key for the table, and then saving the table structure. Donna began documenting the design for the new Billing table by listing each field’s name, data type, and purpose, and will continue to refine the design. See Figure 1-11.

Figure 1-11

Initial design for the Billing table

Field Name	Data Type	Purpose
InvoiceNum	Short Text	Unique number assigned to each invoice; will serve as the table’s primary key
VisitID	Short Text	Unique number assigned to each visit; common field that will be a foreign key to connect to the Visit table
InvoiceAmount	Currency	Dollar amount of each invoice
InvoiceDate	Date/Time	Date the invoice was generated
InvoicePaid	Yes/No	Whether the invoice has been paid or not

You’ll use Donna’s design as a guide for creating the Billing table in the Lakewood database.

To begin creating the Billing table:

If the Navigation Pane is open, click the Shutter Bar Open/Close Button to close it.

On the ribbon, click the Create tab.

In the Tables group, click the Table Design button. A new table named Table1 opens in Design view.

AC 1-5aDefining Fields

When you first create a table in Design view, the insertion point is located in the first row’s Field Name box, ready for you to begin defining the first field in the table. You enter values for the Field Name, Data Type, and Description field properties (optional), and then select values for all other field properties in the Field Properties pane. These other properties will appear when you move to the first row’s Data Type box.

Reference

Defining a Field in Design View

In the Field Name box, type the name for the field, and then press TAB.
Accept the default Short Text data type, or click the arrow and select a different data type for the field. Press TAB.
Enter an optional description for the field, if necessary.
Use the Field Properties pane to type or select other field properties, as appropriate.

The first field you need to define is the InvoiceNum field. This field will be the primary key for the Billing table. Each invoice at Lakewood Community Health Services is assigned a specific five-digit number. Although the InvoiceNum field will contain these number values, the numbers will never be used in calculations; therefore, you’ll assign the Short Text data type to this field. Any time a field contains number values that will not be used in calculations—such as phone numbers, postal codes, and so on—you should use the Short Text data type instead of the Number data type.

To define the InvoiceNum field:

Type InvoiceNum in the first row’s Field Name box, and then press TAB to advance to the Data Type box. The default data type, Short Text, appears highlighted in the Data Type box, which now also contains an arrow, and the field properties for a Short Text field appear in the Field Properties pane. See Figure 1-12.

Figure 1-12Table window after entering the first field name

The right side of the Field Properties pane now provides an explanation for the current property, Data Type.

Trouble? If you make a typing error, you can correct it by clicking to position the insertion point, and then using either BACKSPACE to delete characters to the left of the insertion point or DELETE to delete characters to the right of the insertion point. Then type the correct text.

Because the InvoiceNum field values will not be used in calculations, you will accept the default Short Text data type for the field.

Press TAB to accept Short Text as the data type and to advance to the Description (Optional) box.

Next you’ll enter the Description property value as “Primary key.” The value you enter for the Description property will appear on the status bar when you view the table datasheet. Note that specifying “Primary key” for the Description property does not establish the current field as the primary key; you use a button on the ribbon to specify the primary key in Design view, which you will do later in this session.

Type Primary key in the Description (Optional) box and press ENTER.

At this point, you have entered the first field (InvoiceNum) into the table and are ready to enter the remaining fields into the table.

Figure 1-13InvoiceNum field defined

Donna’s Billing table design (Figure 1-11) shows VisitID as the second field. Because Donna and other staff members need to relate information about invoices to the visit data in the Visit table, the Billing table must include the VisitID field, which is the Visit table’s primary key. Recall that when you include the primary key from one table as a field in a second table to connect the two tables, the field is a foreign key in the second table.

To define the VisitID field:

If the insertion point is not already positioned in the second row’s Field Name box, click the second row’s Field Name box. Once properly positioned, type VisitID in the box, and then press TAB to advance to the Data Type box.

Press TAB to accept Short Text as the field’s data type. Because the VisitID field is a foreign key to the Visit table, you’ll enter “Foreign key” in the Description (Optional) box to help users of the database understand the purpose of this field.

Type Foreign key in the Description (Optional) box and press ENTER.

The third field in the Billing table is the InvoiceAmt field, which will display the dollar amount of each invoice the clinic sends to the patients. The Currency data type is the appropriate choice for this field.

To define the InvoiceAmount field:

In the third row’s Field Name box, type InvoiceAmount and then press TAB to advance to the Data Type box.

Click the Data Type arrow, click Currency in the list, and then press TAB to advance to the Description (Optional) box.

The InvoiceAmount field is not a primary key, nor does it have a relationship with a field in another table, so you do not need to enter a description for this field. If you’ve assigned a descriptive field name and the field does not fulfill a special function (such as primary key), you usually do not enter a value for the optional Description property.

Press TAB to advance to the fourth row’s Field Name box.

The fourth field in the Billing table is the InvoiceDate field. This field will contain the dates on which invoices are generated for the clinic’s patients. You’ll define the InvoiceDate field using the Date/Time data type.

To define the InvoiceDate field:

In the fourth row’s Field Name box, type InvoiceDate and then press TAB to advance to the Data Type box.

You can select a value from the Data Type list as you did for the InvoiceAmount field. Alternately, you can type the property value in the box or type just the first character of the property value.

Type d. The value in the fourth row’s Data Type box changes to “date/Time,” with the letters “ate/Time” highlighted. See Figure 1-14.

Figure 1-14Selecting a value for the Data Type property

Press TAB to advance to the Description (Optional) box. Note that Access changes the value for the Data Type property to “Date/Time.”

Because the InvoiceDate field does not need a special description, press TAB.

The fifth, and final, field to be defined in the Billing table is InvoicePaid. This field will be a Yes/No field to indicate the payment status of each invoice record stored in the Billing table. Recall that the Yes/No data type defines fields that store true/false, yes/no, and on/off field values. When you create a Yes/No field in a table, the default Format property is set to Yes/No.

To define the InvoicePaid field:

In the fifth row’s Field Name box, type InvoicePaid and then press TAB to advance to the Data Type box.

Type y. Access completes the data type as “yes/No.” Press TAB to select the Yes/No data type and move to the Description (Optional) box.

Because the InvoicePaid field does not need a special description, press TAB.

You’ve finished defining the fields for the Billing table. Next, you need to specify the primary key for the table.

AC 1-5bSpecifying the Primary Key

As you learned previously, the primary key for a table uniquely identifies each record in the table.

Reference

Specifying a Primary Key in Design View

Display the table in Design view.
Click in the row for the field you’ve chosen to be the primary key to make it the active field. If the primary key will consist of two or more fields, click the row selector for the first field, press and hold down CTRL, and then click the row selector for each additional primary key field.
On the Table Tools Design tab in the Tools group, click the Primary Key button.

According to Donna’s design, you need to specify InvoiceNum as the primary key for the Billing table. You can do so while the table is in Design view.

To specify InvoiceNum as the primary key:

Click in the row for the InvoiceNum field to make it the current field.

On the Table Tools Design tab in the Tools group, click the Primary Key button. The Primary Key button is highlighted and a key symbol appears in the row selector for the first row, indicating that the InvoiceNum field is the table’s primary key. See Figure 1-15.

Figure 1-15InvoiceNum field selected as the primary key

AC 1-5cRenaming Fields in Design View

Donna has decided to rename the InvoiceAmount field in the Billing table to InvoiceAmt. Since Amt is an appropriate abbreviation for Amount, this new name will be just as readable, yet a little shorter.

To rename a field in Design view:

Click to position the insertion point to the right of the word “InvoiceAmount” in the third row’s Field Name box, and then press BACKSPACE four times to delete the letters “ount.” The name of the fourth field is now InvoiceAm. Now add the final letter by pressing the letter t. The name of the new field is now InvoiceAmt as Donna wants it to be. See Figure 1-16.

Figure 1-16Billing table after renamed field

Click in the row for the InvoiceAmt field to make it the current field.

AC 1-5dSaving the Billing Table Structure

As with the Visit table, the last step in creating a table is to name the table and save the table’s structure. When you save a table structure, the table is stored in the database file (in this case, the Lakewood database file). After saving the table, you can enter data into it. According to Donna’s plan, you need to save the table you’ve defined as “Billing.”

To save, name, and close the Billing table:

On the Quick Access Toolbar, click the Save button . The Save As dialog box opens.

With the default name Table1 selected in the Table Name box, type Billing, and then click OK. The tab for the table now displays the name “Billing,” and the Billing table design is saved in the Lakewood database.

Click the Close ‘Billing’ button on the object tab. The Billing table closes, and the main portion of the Access window is now blank because no database object is currently open. The Lakewood database file is still open, as indicated by the filename in the Access window title bar.

You have now successfully created and saved the structures for the Visit and Billing tables; however, you have not yet added any data to these tables. You can view and work with these objects in the Navigation Pane.

To view objects in the Lakewood database:

On the Navigation Pane, click the Shutter Bar Open/Close Button to open it. See Figure 1-17.

Figure 1-17Visit and Billing tables (database objects) displayed in the Navigation Pane

AC 1-6Closing a Table and Exiting Access

When you are finished working in an Access table, it’s a good idea to close the table so that you do not make unintended changes to the table data. You can close a table by clicking its Close button on the object tab, as you did earlier. Or, if you want to close the Access program as well, you can click the program’s Close button. When you do, any open tables are closed, the active database is closed, and you exit the Access program.

To close any opened tables and exit Access:

Click the Close button on the program window title bar. Any opened tables would close, along with the Lakewood database, and then the Access program closes.

Insight

Saving a Database

Unlike the Save buttons in other Office programs, the Save button on the Quick Access Toolbar in Access does not save the active document (database). Instead, you use the Save button to save the design of an Access object, such as a table (as you saw earlier), or to save datasheet format changes, such as resizing columns. Access does not have or need a button or option you can use to save the active database.

Access saves changes to the active database automatically when you change or add a record or close the database. If your database is stored on a removable storage device, such as a USB drive, you should never remove the device while the database file is open. If you do, Access will encounter problems when it tries to save the database, which might damage the database. Make sure you close the database first before removing the storage device.

It is possible to save a database with a different name. To do so, you would click the File tab to open Backstage view, and then click the Save As option. You save the database in the default database format unless you select a different format, so click the Save As button to open the Save As dialog box. Enter the new name for the database, choose the location for saving the file, and then click Save. The database is saved with a new name and is stored in the specified location.

Now that you’ve become familiar with database concepts and Access, and created the Lakewood database and the structures for the Visit and Billing tables, Donna wants you to add records to the Visit table and work with the data stored in it to create database objects including a query, form, and report. You’ll complete these tasks in the next session.

AC 1-7Session 1.2 Visual Overview: The Create Tab Options

AC 1-8Entering Data into Tables

With the fields in place for the Visit table, you can now enter the field values for each record. However, if you closed Access, as instructed, after the previous session, you must first open Access and the Lakewood database to be able to work with the Visit table. If you did not close Access in the previous session and the Lakewood database is still open (see previous Figure 1-17), you may skip the steps below that open Access and the Lakewood database, and go directly to the steps to enter data into the Visit table.

Reference

Opening a Database

Start Access and display the Recent screen in Backstage view.
Click the name of the database you want to open in the list of recently opened databases.

Start Access and display the Recent screen in Backstage view.
In the navigation bar, click Open Other Files to display the Open screen.
Click the Browse button to open the Open dialog box, and then navigate to the drive and folder containing the database file you want to open.
Click the name of the database file you want to open, and then click Open.

To open Access and Lakewood database:

On the Windows taskbar, click the Start button . The Start menu opens.

Click Access.

Access starts and displays the Recent screen in Backstage view. You may choose the Lakewood database from the Recent list (with its location listed below the database name), or click Open Other Files to display the Open screen in Backstage view and browse to your database and location. If you choose to open the Lakewood database from the Recent list, skip steps 4, 5, and 6.

If you choose to open other files from step 3, on the Open screen, click Browse. The Open dialog box opens, showing folder information for your computer.

Trouble? If you are storing your files on OneDrive, click OneDrive, and then sign in if necessary.

Navigate to the drive that contains your Data Files.

Navigate to the Access1 > Module folder, click the database file named Lakewood, and then click Open. The Lakewood database opens in the Access program window.

Trouble? If a security warning appears below the ribbon indicating that some active content has been disabled, click the Enable Content button. Access provides this warning because some databases might contain content that could harm your computer. Because the Lakewood database does not contain objects that could be harmful, you can open it safely. If you are accessing the file over a network, you might also see a dialog box asking if you want to make the file a trusted document; click Yes.

Note that the Lakewood database contains two objects, the Billing and Visit tables you created at the end of the previous session (see Figure 1-17). The next step is for you to open the Visit table to begin adding records.

To open the Visit table:

In the Navigation Pane, double-click Visit to open the Visit table in Datasheet view.

On the Navigation Pane, click the Shutter Bar Open/Close Button to close the pane.

Click the first row value for the VisitID field. See Figure 1-18.

Figure 1-18Visit table opened and ready to enter data

You are now ready to begin adding records and are positioned in the first field (VisitID) of the first record. Donna requests that you enter eight records into the Visit table, as show in Figure 1-19.

Figure 1-19

Visit table records

VisitID	PatientID	VisitDate	Reason	WalkIn
1495	13310	12/23/2020	Rhinitis	Yes
1450	13272	10/26/2020	Influenza	Yes
1461	13250	11/3/2020	Dermatitis	Yes
1615	13308	4/1/2021	COPD management visit	No
1596	13299	3/24/2021	Pneumonia	Yes
1567	13283	2/26/2021	Annual wellness visit	No
1499	13264	12/28/2020	Hypotension	No
1475	13261	11/19/2020	Annual wellness visit	No

To enter the first record for the Visit table:

In the first row for the VisitID field, type 1495 (the VisitID field value for the first record), and then press TAB. Access adds the field value and moves the insertion point to the right, into the PatientID column. See Figure 1-20.

Figure 1-20First field value entered

Trouble? If you make a mistake when typing a value, use BACKSPACE to delete characters to the left of the insertion point or use DELETE to delete characters to the right of the insertion point. Then type the correct value. To correct a value by replacing it entirely, press ESC, and then type the correct value.

Notice the pencil symbol that appears in the row selector for the new record. The pencil symbol indicates that the record is being edited. Also notice the star symbol that appears in the row selector for the second row. The star symbol identifies the second row as the next row available for a new record.

Type 13310 (the PatientID field value for the first record), and then press TAB. Access enters the field value and moves the insertion point to the VisitDate column.

Type 12/23/20 (the VisitDate field value for the first record), and then press TAB. Access displays the year as “2020” even though you entered only the final two digits of the year. This is because the VisitDate field has the Date/Time data type, which automatically formats dates with four-digit years.

Type Rhinitis (the Reason field value for the first record), and then press TAB to move to the WalkIn column.

Recall that the WalkIn field is a Yes/No field. Notice the check box displayed in the WalkIn column. By default, the value for any Yes/No field is “No”; therefore, the check box is initially empty. For Yes/No fields with check boxes, you press TAB to leave the check box unchecked, or you press SPACEBAR to insert a checkmark in the check box. The record you are entering in the table is for a walk-in visit, so you need to insert a checkmark in the check box to indicate “Yes.”

Press SPACEBAR to insert a checkmark, and then press TAB. The first record is entered into the table, and the insertion point is positioned in the VisitID field for the second record. The pencil symbol is removed from the first row because the record in that row is no longer being edited. The table is now ready for you to enter the second record. See Figure 1-21.

Figure 1-21Datasheet with first record entered

Now you can enter the remaining seven records in the Visit table.

To enter the remaining records in the Visit table:

Referring to Figure 1-19, enter the values for records 2 through 8, pressing TAB to move from field to field and to the next row for a new record. Keep in mind that you do not have to type all four digits of the year in the VisitDate field values; you can enter only the final two digits, and Access will display all four. Also, for any WalkIn field values of “No,” be sure to press TAB to leave the check box empty.

Trouble? If you enter a value in the wrong field by mistake, such as entering a Reason field value in the VisitDate field, a menu might open with options for addressing the problem. If this happens, click the “Enter new value” option in the menu. You’ll return to the field with the incorrect value selected, which you can then replace by typing the correct value.

Notice that not all of the Reason field values are fully displayed. To see more of the table datasheet and the full field values, you’ll resize the Reason column.

Place the pointer on the vertical line to the right of the Reason field name until the pointer changes to the column resizing pointer , and then double-click the vertical line. All the Reason field values are now fully displayed. See Figure 1-22.

Figure 1-22Datasheet with eight records entered

When you resize a datasheet column by double-clicking the column dividing line, you are sizing the column to its best fit—that is, so the column is just wide enough to display the longest visible value in the column, including the field name.

Compare your table to the one in Figure 1-22. If any of the field values in your table do not match those shown in the figure, you can correct a field value by clicking to position the insertion point in the value, and then using BACKSPACE or DELETE to delete incorrect text. Type the correct text and press ENTER. To correct a value in the WalkIn field, click the check box to add or remove the checkmark as appropriate. Also, be sure the spelling and capitalization of field names in your table match those shown in the figure exactly and that there are no spaces between words. To correct a field name, double-click it to select it, and then type the correct name; or use the Rename Field option on the shortcut menu to rename a field with the correct name.

Remember that Access automatically saves the data stored in a table; however, you must save any new or modified structure to a table. Even though you have not clicked the Save button, your data has already been saved. To ensure this is the case, you can close the table and then reopen it.

To close and reopen Visit table:

Click the Close ‘Visit’ button on the object tab for the Visit table. When asked if you would like to save the changes to the layout of the Visit table, click Yes. The Visit table closes.

On the Navigation Pane, click the Shutter Bar Open/Close Button to open it.

In the Navigation Pane, double-click Visit to open the Visit table in Datasheet view.

Notice that after you closed and reopened the Visit table, Access sorted and displayed the records in order by the values in the VisitID field because it is the primary key. If you compare your screen to Figure 1-22, which shows the records in the order you entered them, you’ll see that the current screen shows the records in order by the VisitID field values.

Donna asks you to add two more records to the Visit table. When you add a record to an existing table, you must enter the new record in the next row available for a new record; you cannot insert a row between existing records for the new record. In a table with just a few records, such as the Visit table, the next available row is visible on the screen. However, in a table with hundreds of records, you would need to scroll the datasheet to see the next row available. The easiest way to add a new record to a table is to use the New button, which scrolls the datasheet to the next row available so you can enter the new record.

To enter additional records in the Visit table:

If necessary, click the first record’s VisitID field value (1450 ) to make it the current record.

In the Records group, click the New button. The insertion point is positioned in the next row available for a new record, which in this case is row 9. See Figure 1-23.

Figure 1-23Entering a new record

With the insertion point in the VisitID field for the new record, type 1548 and then press TAB.

Complete the entry of this record by entering each value shown below, pressing TAB to move from field to field:

PatientID = 13301

VisitDate = 2/10/2021

Reason = Hypothyroidism

WalkIn = No (unchecked)

Enter the values for the next new record, as follows, and then press TAB after entering the WalkIn field value:

VisitID = 1588

PatientID = 13268

VisitDate = 3/19/2021

Reason = Cyst removal

WalkIn = Yes (checked)

Your datasheet should look like the one shown in Figure 1-24.

Figure 1-24Datasheet with additional records entered

The new records you added appear at the end of the table, and are not sorted in order by the primary key field values. For example, VisitID 1548 should be the sixth record in the table, placed between VisitID 1499 and VisitID 1567. When you add records to a table datasheet, they appear at the end of the table. The records are not displayed in primary key order until you either close and reopen the table or switch views.

Click the Close ‘Visit’ button on the object tab. The Visit table closes; however, it is still listed in the Navigation Pane.

Double-click Visit to open the table in Datasheet view. See Figure 1-25.

Figure 1-25Table with 10 records entered and displayed in primary key order

The two records you added, with VisitID field values of 1548 and 1588, now appear in the correct primary key order. The table contains a total of 10 records, as indicated by the Current Record box at the bottom of the datasheet. The Current Record box displays the number of the current record as well as the total number of records in the table.

Each record contains a unique VisitID value because this field is the primary key. Other fields, however, can contain the same value in multiple records; for example, the Reason field has two values of “Annual wellness visit.”

Click the Close button on the program window title bar. The Visit table, along with the Lakewood database, close, and then the Access program closes.

AC 1-9Copying Records from Another Access Database

When you created the Visit table, you entered records directly into the table datasheet. There are many other ways to enter records in a table, including copying and pasting records from a table into the same database or into a different database. To use this method, however, the two tables must have the same structure—that is, the tables must contain the same fields, with the same design, in the same order.

Donna has already created a table named Appointment that contains additional records with visit data. The Appointment table is contained in a database named Support_AC_1_Donna.accdb located in the Access1 > Module folder included with your Data Files. The Appointment table has the same table structure as the Visit table you created.

Your next task is to copy the records from the Appointment table and paste them into your Visit table. To do so, you need to open the Support_AC_1_Donna.accdb database.

To copy the records from the Appointment table:

On the Windows taskbar, click the Start button . The Start menu opens.

Click Access.

Click Open Other Files to display the Open screen in Backstage view.

On the Open screen, click Browse. The Open dialog box opens, showing folder information for your computer.

Trouble? If you are storing your files on OneDrive, click OneDrive, and then log in if necessary.

Navigate to the drive that contains your Data Files.

Navigate to the Access1 > Module folder, click the database file Support_AC_1_Donna.accdb, and then click Open. The Support_AC_1_Donna database opens in the Access program window. Note that the database contains only one object, the Appointment table.

Trouble? If a security warning appears below the ribbon indicating that some active content has been disabled, click the Enable Content button. Access provides this warning because some databases might contain content that could harm your computer. Because the Support_AC_1_Donna.accdb database does not contain objects that could be harmful, you can open it safely. If you are accessing the file over a network, you might also see a dialog box asking if you want to make the file a trusted document; click Yes.

In the Navigation Pane, double-click Appointment to open the Appointment table in Datasheet view. The table contains 76 records and the same five fields, with the same characteristics, as the fields in the Visit table. See Figure 1-26.

Figure 1-26Appointment Table in the Support_AC_1_Donna Database

Donna wants you to copy all the records in the Appointment table. You can select all the records by clicking the datasheet selector, which is the box to the left of the first field name in the table datasheet, as shown in Figure 1-26.

Click the datasheet selector to the left of the VisitID field. All the records in the table are selected.

In the Clipboard group, click the Copy button to copy all the records to the Clipboard.

Click the Close ‘Appointment’ button on the object tab. A dialog box may open asking if you want to save the data you copied to the Clipboard. This dialog box opens only when you copy a large amount of data to the Clipboard. If asked, click Yes. If opened, the dialog box closes, and then the Appointment table closes.

With the records copied to the Clipboard, you can now paste them into the Visit table. First you need to close the Support_AC_1_Donna.accdb database while keeping the Access program open, and then open the Lakewood database.

To close the Support_AC_1_Donna.accdb database and then paste the records into the Visit table:

Click the File tab to open Backstage view, and then click Close in the navigation bar to close the Support_AC_1_Donna.accdb database. You return to a blank Access program window, and the Home tab is the active tab on the ribbon.

Click the File tab to return to Backstage view, and then click Open in the navigation bar. Recent is selected on the Open screen, and the recently opened database files are listed. This list should include the Lakewood database.

Click Lakewood to open the Lakewood database file.

Trouble? If the Lakewood database file is not in the list of recent files, click Browse. In the Open dialog box, navigate to the drive and folder where you are storing your files, and then open the Lakewood database file.

Trouble? If the security warning appears below the ribbon, click the Enable Content button, and then, if necessary, click Yes to identify the file as a trusted document.

In the Navigation Pane, double-click Visit to open the Visit table in Datasheet view.

On the Navigation Pane, click the Shutter Bar Open/Close Button to close the pane.

Position the pointer on the star symbol in the row selector for row 11 (the next row available for a new record) until the pointer changes to a right-pointing arrow , and then click to select the row.

In the Clipboard group, click the Paste button. The pasted records are added to the table, and a dialog box opens asking you to confirm that you want to paste all the records (76 total).

Trouble? If the Paste button isn’t active, click the row selection pointer on the row selector for row 11, making sure the entire row is selected, and then repeat Step 7.

Click Yes. The dialog box closes, and the pasted records are selected. See Figure 1-27. Notice that the table now contains a total of 86 records—10 records that you entered previously and 76 records that you copied and pasted.

Figure 1-27Visit table after copying and pasting records

Not all the Reason field values are completely visible, so you need to resize this column to its best fit.

Place the pointer on the column dividing line to the right of the Reason field name until the pointer changes to the column resizing pointer , and then double-click the column dividing line. The Reason field values are now fully displayed.

AC 1-10Navigating a Datasheet

The Visit table now contains 86 records, but only some of the records are visible on the screen. To view fields or records not currently visible on the screen, you can use the horizontal and vertical scroll bars to navigate the data. The navigation buttons, shown in Figure 1-27 and also described in Figure 1-28, provide another way to move vertically through the records. The Current Record box appears between the two sets of navigation buttons and displays the number of the current record as well as the total number of records in the table. Figure 1-28 shows which record becomes the current record when you click each navigation button. The New (blank) record button works the same way as the New button on the Home tab, which you used earlier to enter a new record in the table.

Figure 1-28

Navigation buttons

Navigation Button	Record Selected
	First record
	Previous record
	Next record
	Last record
	New (blank) record

Donna suggests that you use the various navigation techniques to move through the Visit table and become familiar with its contents.

To navigate the Visit datasheet:

Click the first record’s VisitID field value (1450 ). The Current Record box shows that record 1 is the current record.

Click the Next record button . The second record is now highlighted, which identifies it as the current record. The second record’s value for the VisitID field is selected, and the Current Record box displays “2 of 86” to indicate that the second record is the current record.

Click the Last record button . The last record in the table, record 86, is now the current record.

Drag the scroll box in the vertical scroll bar up to the top of the bar. Record 86 is still the current record, as indicated in the Current Record box. Dragging the scroll box changes the display of the table datasheet, but does not change the current record.

Drag the scroll box in the vertical scroll bar back down until you can see the end of the table and the current record (record 86).

Click the Previous record button . Record 85 is now the current record.

Click the First record button . The first record is now the current record and is visible on the screen.

Earlier you resized the Reason column to its best fit, to ensure all the field values were visible. However, when you resize a column to its best fit, the column expands to fully display only the field values that are visible on the screen at that time. If you move through the complete datasheet and notice that not all of the field values are fully displayed after resizing the column, you need to resize the column again.

Scroll down through the records and observe if the field values for the Reason field are fully displayed. The Reason field values for visit 1595 and visit 1606 are not fully displayed. With these records displayed, place the pointer on the column dividing line to the right of the Reason field name until the pointer changes to the column resizing pointer , and then double-click the column dividing line. The field values are now fully displayed.

The Visit table now contains all the data about patient visits for Lakewood Community Health Services. To better understand how to work with this data, Donna asks you to create simple objects for the other main types of database objects—queries, forms, and reports.

AC 1-11Creating a Simple Query

A query is a question you ask about the data stored in a database. When you create a query, you tell Access which fields you need and what criteria it should use to select the records that will answer your question. Then Access displays only the information you want, so you don’t have to navigate through the entire database for the information. In the Visit table, for example, Donna might create a query to display only those records for visits that occurred in a specific month. Even though a query can display table information in a different way, the information still exists in the table as it was originally entered.

Donna wants to see a list of all the visit dates and reasons for visits in the Visit table. She doesn’t want the list to include all the fields in the table, such as PatientID and WalkIn. To produce this list for Donna, you’ll use the Simple Query Wizard to create a query based on the Visit table.

To start the Simple Query Wizard:

On the ribbon, click the Create tab.

In the Queries group, click the Query Wizard button. The New Query dialog box opens.

Make sure Simple Query Wizard is selected, and then click OK. The first Simple Query Wizard dialog box opens. See Figure 1-29.

Figure 1-29First Simple Query Wizard dialog box

Because the Visit table is open in the Lakewood database, it is listed in the Tables/Queries box by default. If the database contained more objects, you could click the Tables/Queries arrow and choose another table or a query as the basis for the new query you are creating. In this case you could choose the Billing table; however, the Visit table contains the fields you need. The Available Fields box lists all the fields in the Visit table.

Trouble? If the Visit table is not the default source for the query, click the Tables/Queries arrow to choose the Visit table (Table: Visit) from the list.

You need to select fields from the Available Fields box to include them in the query. To select fields one at a time, click a field and then click the Select Single Field button. The selected field moves from the Available Fields box on the left to the Selected Fields box on the right. To select all the fields, click the Select All Fields button. If you change your mind or make a mistake, you can remove a field by clicking it in the Selected Fields box and then clicking the Remove Single Field button. To remove all fields from the Selected Fields box, click the Remove All Fields button.

Each Simple Query Wizard dialog box contains buttons that allow you to move to the previous dialog box (Back button), move to the next dialog box (Next button), or cancel the creation process (Cancel button). You can also finish creating the object (Finish button) and accept the wizard’s defaults for the remaining options.

Donna wants her query results list to include data from only the following fields: VisitID, VisitDate, and Reason. You need to select these fields to include them in the query.

To create the query using the Simple Query Wizard:

Click VisitID in the Available Fields box to select the field (if necessary), and then click the Select Single Field button. The VisitID field moves to the Selected Fields box.

Repeat Step 1 for the fields VisitDate and Reason, and then click Next. The second, and final, Simple Query Wizard dialog box opens and asks you to choose a name (title) for your query. The suggested name is “Visit Query” because the query you are creating is based on the Visit table. You’ll change the suggested name to “VisitList.”

Click at the end of the suggested name, use BACKSPACE to delete the word “Query” and the space, and then type List. Now you can view the query results.

Click Finish to complete the query. The query results are displayed in Datasheet view, on a new tab named “VisitList.” A query datasheet is similar to a table datasheet, showing fields in columns and records in rows—but only for those fields and records you want to see, as determined by the query specifications you select.

Place the pointer on the column divider line to the right of the Reason field name until the pointer changes to the column resizing pointer , and then double-click the column divider line to resize the Reason field. See Figure 1-30.

Figure 1-30Query results

The VisitList query datasheet displays the three fields in the order you selected them in the Simple Query Wizard, from left to right. The records are listed in order by the primary key field, VisitID. Even though the query datasheet displays only the three fields you chose for the query, the Visit table still includes all the fields for all records.

Navigation buttons are located at the bottom of the window. You navigate a query datasheet in the same way that you navigate a table datasheet.

Click the Last record button . The last record in the query datasheet is now the current record.

Click the Previous record button . Record 85 in the query datasheet is now the current record.

Click the First record button . The first record is now the current record.

Click the Close ‘VisitList’ button on the object tab. A dialog box opens asking if you want to save the changes to the layout of the query. This dialog box opens because you resized the Reason column.

Click Yes to save the query layout changes and close the query.

The query results are not stored in the database; however, the query design is stored as part of the database with the name you specified. You can re-create the query results at any time by opening the query again. When you open the query later, the results displayed will reflect up-to-date information to include any new records entered in the Visit table.

Donna asks you to display the query results again; however, this time she would like to list the records in descending order showing the most current VisitID first. The records are currently displayed in ascending order by VisitID, which is the primary key for the Visit table. In order to display the records in descending order, you can sort the records in Query Datasheet view.

To sort records in a query datasheet:

On the Navigation Pane, click the Shutter Bar Open/Close Button to open it.

In the Navigation Pane, double-click VisitList to open the VisitList query in Datasheet view.

On the Navigation Pane, click the Shutter Bar Open/Close Button to close it.

On the ribbon, click the Home tab. The first record value in the VisitID field is highlighted; therefore, VisitID is the current field. Also note the data in the first record (VisitID: 1450; VisitDate: 10/26/2020; and Reason: Influenza).

In the Sort & Filter group, click the Descending button. The records are sorted in descending order by the current field (VisitID). Because the list of records is now sorted in descending order, the original first record (VisitID 1450) should now be the last record.

Scroll down the list of records and see that the same data for VisitID 1450 is now in the last record. Donna has decided not to keep the data sorted in descending order and wants to return to ascending order.

In the Sort & Filter group, click the Remove Sort button. The data returns to its original state in ascending order with VisitID 1450 (and its corresponding data) listed in the first record.

Click the Close ‘VisitList’ button on the object tab for the VisitList query. When asked if you would like to save the changes to the design of the VisitList query, click No. The VisitList query closes.

Next, Donna asks you to create a form for the Visit table so the staff at Lakewood Community Health Services can use the form to enter and work with data in the table easily.

AC 1-12Creating a Simple Form

As noted earlier, you use a form to enter, edit, and view records in a database. Although you can perform these same functions with tables and queries, forms can present data in many customized and useful ways.

Donna wants a form for the Visit table that shows all the fields for one record at a time, with fields listed one below another in a column. This type of form will make it easier for her staff to focus on all the data for a particular visit. You’ll use the Form Wizard to create this form quickly and easily.

To create the form using the Form Wizard

Make sure the Visit table is still open in Datasheet view.

Trouble? If the Visit table is not open, click the Shutter Bar Open/Close Button to open the Navigation Pane. Double-click Visit to open the Visit table in Datasheet view. Click the Shutter Bar Open/Close Button to close the pane.

On the ribbon, click the Create tab if necessary.

In the Forms group, click the Form Wizard button. The first Form Wizard dialog box opens. Make sure the Visit table is the default data source for the form.

Trouble? If the Visit table is not the default source for the form, click the Tables/Queries arrow to choose the Visit table (Table: Visit) from the list.

The first Form Wizard dialog box is very similar to the first Simple Query Wizard dialog box you used in creating a query.

Click the Select All Fields button to move all the fields to the Selected Fields box.

Click Next to display the second Form Wizard dialog box, in which you select a layout for the form. See Figure 1-31.

Figure 1-31Choosing a layout for the form

The layout choices are Columnar, Tabular, Datasheet, and Justified. A sample of the selected layout appears on the left side of the dialog box.

Click each option button and review the corresponding sample layout.

Because Donna wants to arrange the form data in a column with each field listed one below another, click the Columnar option button (if necessary), and then click Next.

The third and final Form Wizard dialog box shows the Visit table’s name as the default name for the form name. “Visit” is also the default title that will appear on the tab for the form.

You’ll use “VisitData” as the form name, and because you don’t need to change the form’s design at this point, you’ll display the form.

Click to position the insertion point to the right of ‘Visit’ in the box, type Data, and then click the Finish button.

The completed form opens in Form view, displaying the values for the first record in the Visit table. The Columnar layout places the field captions in labels on the left and the corresponding field values in boxes to the right, which vary in width depending on the size of the field. See Figure 1-32.

Figure 1-32VisitData form in Form view

The form displays one record at a time in the Visit table, providing another view of the data that is stored in the table and allowing you to focus on the values for one record. Access displays the field values for the first record in the table and selects the first field value (VisitID), as indicated by the value being highlighted. Each field name appears on a separate line and on the same line as its field value, which appears in a box to the right. Depending on your computer’s settings, the field value boxes in your form might be wider or narrower than those shown in the figure. As indicated in the status bar, the form is displayed in Form view. Later, you will work with a form in Layout view, where you can make design changes to the form while it is displaying data.

To view, enter, and maintain data using a form, you must know how to move from field to field and from record to record. Notice that the form contains navigation buttons, similar to those available in Datasheet view, which you can use to display different records in the form. You’ll use these now to navigate the form; then you’ll save and close the form.

To navigate, save, and close the form:

Click the Next record button . The form now displays the values for the second record in the Visit table.

Click the Last record button to move to the last record in the table. The form displays the information for VisitID 1623.

Click the Previous record button to move to record 85.

Click the First record button to return to the first record in the Visit table.

Click the Close ‘VisitData’ button on the object tab to close the form.

Insight

Saving Database Objects

In general, it is best to save a database object—query, form, or report—only if you anticipate using the object frequently or if it is time-consuming to create, because all objects use storage space and increase the size of the database file. For example, you most likely would not save a form you created with the Form tool because you can re-create it easily with one click. (However, for the purposes of this text, you usually need to save the objects you create.)

Donna would like to see the information in the Visit table presented in a more readable and professional format. You’ll help Donna by creating a report.

AC 1-13Creating a Simple Report

A report is a formatted printout (or screen display) of the contents of one or more tables or queries. You’ll use the Report Wizard to guide you through producing a report based on the Visit table for Donna. The Report Wizard creates a report based on the selected table or query.

To create the report using the Report Wizard:

On the ribbon, click the Create tab.

In the Reports group, click the Report Wizard button. The first Report Wizard dialog box opens. Make sure the Visit table is the default data source for the report.

Trouble? If the Visit table is not the default source for the report, click the Tables/Queries arrow to choose the Visit table (Table: Visit) from the list.

The first Report Wizard dialog box is very similar to the first Simple Query Wizard dialog box you used in creating a query, and to the first Form Wizard dialog box you used in creating a form.

You select fields in the order you want them to appear on the report. Donna wants to include only the VisitID, PatientID, and Reason fields (in that order) on the report.

Click VisitID in the Available Fields box (if necessary), and then click the Select Single Field button to move the field to the Selected Fields box.

Repeat step 3 to add the PatientID and Reason fields to the Selected Fields box. The VisitID, PatientID, and Reason fields (in that order) are listed in the Selected Fields box to add to the report. See Figure 1-33.

Figure 1-33First Report Wizard dialog box

Click Next to open the second Report Wizard dialog box, which asks whether you want to add grouping levels to your report. This concept will be discussed later; Donna’s report does not have any grouping levels.

Click Next to proceed to the third Report Wizard dialog box, which asks whether to sort records in a certain order by a particular field on the report. Donna wants to list the records by the VisitID field in ascending order. Access allows up to four levels of sorting, although Donna wants only one.

Click the arrow in the first sort option box, and then click VisitID. See Figure 1-34. The default option for sorting on the VisitID field is ascending.

Figure 1-34Third Report Wizard dialog box

Click Next to proceed to the fourth Report Wizard dialog box, which asks you to select the layout for the report. You can click a Layout option to display an example of the layout.

Click the Tabular option button (if necessary). Later you can select other options for a report; however, this report uses the current default options.

Click Next to proceed to the final Report Wizard dialog box, in which you name the report. Donna wants to name the report “VisitDetails.”

Click to position the insertion point to the right of ‘Visit’ in the box, and then type Details. Click Finish to preview the report. See Figure 1-35.

Figure 1-35Report in Print Preview

The report shows each field in a column, with the field values for each record in a row, similar to a table or query datasheet. However, a report offers a more visually appealing format for the data. The report is currently shown in Print Preview. Print Preview shows exactly how the report will look when printed. Print Preview also provides page navigation buttons at the bottom of the window, similar to the navigation buttons you’ve used to move through records in a table, query, and form.

To navigate the report in Print Preview:

Click the Next Page button . The second page of the report is displayed in Print Preview.

Click the Last Page button to move to the last page of the report.

Drag the scroll box in the vertical scroll bar down until the bottom of the report page is displayed. The current date is displayed at the bottom left of the page. The notation “Page 3 of 3” appears at the bottom right of the page, indicating that you are on page 3 out of a total of 3 pages in the report.

Trouble? Depending on the printer you are using, your report might have more or fewer pages, and some of the pages might be blank. If so, don’t worry. Different printers format reports in different ways, sometimes affecting the total number of pages and the number of records printed per page.

Click the First Page button to return to the first page of the report, and then drag the scroll box in the vertical scroll bar up to display the top of the report.

AC 1-13aPrinting a Report

After creating a report, you might need to print it to distribute it to others who need to view the report’s contents. You can print a report without changing any print settings, or display the Print dialog box and select options for printing.

Reference

Printing a Report

Open the report in any view, or select the report in the Navigation Pane.
Click the File tab to display Backstage view, click Print, and then click Quick Print to print the report with the default print settings.

Open the report in any view, or select the report in the Navigation Pane.
Click the File tab, click Print, and then click Print; or, if the report is displayed in Print Preview, click the Print button in the Print group on the Print Preview tab. The Print dialog box opens, in which you can select the options you want for printing the report.

Donna asks you to print the entire report with the default settings, so you’ll use the Quick Print option in Backstage view.

Note: To complete the following steps, your computer must be connected to a printer. Check with your instructor first to see if you should print the report.

To print the report and then close it:

On the ribbon, click the File tab to open Backstage view.

In the navigation bar, click Print to display the Print screen, and then click Quick Print. The report prints with the default print settings, and you return to the report in Print Preview.

Trouble? If your report did not print, make sure that your computer is connected to a printer, and that the printer is turned on and ready to print. Then repeat Steps 1 and 2.

Click the Close ‘VisitDetails’ button on the object tab to close the report.

Click the Close ‘Visit’ button on the object tab to close the Visit table.

Trouble? If you are asked to save changes to the layout of the table, click Yes.

You can also use the Print dialog box to print other database objects, such as table and query datasheets. Most often, these objects are used for viewing and entering data, and reports are used for printing the data in a database.

AC 1-14Viewing Objects in the Navigation Pane

The Lakewood database now contains five objects—the Billing table, the Visit table, the VisitList query, the VisitData form, and the VisitDetails report. When you work with the database file—such as closing it, opening it, or distributing it to others—the file includes all the objects you created and saved in the database. You can view and work with these objects in the Navigation Pane.

To view the objects in the Lakewood database:

On the Navigation Pane, click the Shutter Bar Open/Close Button to open the pane. See Figure 1-36.

Figure 1-36Lakewood database objects displayed in the Navigation Pane

The Navigation Pane currently displays the default category, All Access Objects, which lists all the database objects in the pane. Each object type (Tables, Queries, Forms, and Reports) appears in its own group. Each database object (the Billing table, the Visit table, the VisitList query, the VisitData form, and the VisitDetails report) has a unique icon to its left to indicate the type of object. This makes it easy for you to identify the objects and choose which one you want to open and work with.

The arrow on the All Access Objects bar displays a menu with options for various ways to group and display objects in the Navigation Pane. The Search box enables you to enter text for Access to find; for example, you could search for all objects that contain the word “Visit” in their names. Note that Access searches for objects only in the categories and groups currently displayed in the Navigation Pane.

As you continue to build the Lakewood database and add more objects to it in later modules, you’ll use the options in the Navigation Pane to manage those objects.

AC 1-15Using Microsoft Access Help

Access includes a Help system you can use to search for information about specific program features. You start Help by clicking the Microsoft Access Help tab on the ribbon, or by pressing F1.

You’ll use Help now to learn more about the Navigation Pane.

To search for information about the Navigation Pane in Help:

On the ribbon, click the Help tab. Multiple buttons are displayed, with the first being the Help button. Click the Help button. The Access Help window opens.

Click in the Search box (if necessary), type Navigation Pane, and then press ENTER. The Access Help window displays a list of topics related to the Navigation Pane.

Click the topic Show or hide the Navigation Pane in Access. The Access Help window displays the article you selected. See Figure 1-37.

Figure 1-37Article displayed in the Access Help window

Trouble? If the article on managing database objects is not listed in your Help window, choose another article related to the Navigation Pane to read. Your Help window may also look different from the figure.

Scroll through the article to read detailed information about working with the Navigation Pane.

When finished, click the Close button on the Access Help window to close it.

The Access Help system is an important reference tool for you to use if you need additional information about databases in general, details about specific Access features, or support with problems you might encounter.

AC 1-16Managing a Database

One of the main tasks involved in working with database software is managing your databases and the data they contain. Some of the activities involved in database management include compacting and repairing a database and backing up and restoring a database. By managing your databases, you can ensure that they operate in the most efficient way, that the data they contain is secure, and that you can work with the data effectively.

AC 1-16aCompacting and Repairing a Database

Whenever you open an Access database and work in it, the size of the database increases. Further, when you delete records or when you delete or replace database objects—such as queries, forms, and reports—the storage space that had been occupied by the deleted or replaced records or objects does not automatically become available for other records or objects. To make the space available, and to increase the speed of data retrieval, you must compact the database. Compacting a database rearranges the data and objects in a database to decrease its file size, thereby making more storage space available and enhancing the performance of the database. Figure 1-38 illustrates the compacting process.

Figure 1-38Compacting a database

When you compact a database, Access repairs the database at the same time, if necessary. In some cases, Access detects that a database is damaged when you try to open it and gives you the option to compact and repair it at that time. For example, the data in your database might become damaged, or corrupted, if you exit the Access program suddenly by turning off your computer. If you think your database might be damaged because it is behaving unpredictably, you can use the Compact & Repair Database option to fix it.

Reference

Compacting and Repairing a Database

Make sure the database file you want to compact and repair is open.
Click the File tab to display the Info screen in Backstage view.
Click the Compact & Repair Database button.

Access also allows you to set an option to compact and repair a database file automatically every time you close it. The Compact on Close option is available in the Current Database section of the Access Options dialog box, which you open from Backstage view by clicking the Options command in the navigation bar. By default, the Compact on Close option is turned off.

Next, you’ll compact the Lakewood database manually using the Compact & Repair Database option. This will make the database smaller and allow you to work with it more efficiently. After compacting the database, you’ll close it.

To compact and repair the Lakewood database:

On the ribbon, click the File tab to open the Info screen in Backstage view.

Click the Compact & Repair Database button. Although nothing changes on the screen, Access compacts the Lakewood database, making it smaller, and repairs it at the same time. The Home tab is again the active tab on the ribbon.

Click the File tab to return to Backstage view, and then click Close in the navigation bar. The Lakewood database closes.

Option 1

Low Cost Option

Download this past answer in few clicks

24.99 USD

PURCHASE SOLUTION

Already member? Sign In

Option 2

Custom new solution created by our subject matter experts

GET A QUOTE

rated 5 stars

Purchased 3 times

Completion Status 100%

menu

New Perspectives Access 2019 Module 1 Textbook Project AC 1-3Starting Access and Creating a Database Now that you’ve learned some database terms and concepts, you’re ready to start Access and create the Lakewood database for Donna

MS Access

AC 1-3aWorking in Touch Mode

AC 1-4Creating a Table in Datasheet View

Creating a Table in Datasheet View

Plan for the Visit table

Decision Making: Naming Fields in Access Tables

AC 1-4aRenaming the Default Primary Key Field

Buttons and Labels on the Ribbon

AC 1-4bChanging the Data Type of the Default Primary Key Field

AC 1-4cAdding New Fields

AC 1-4dSaving the Visit Table Structure

Saving a Table

AC 1-5Creating a Table in Design View

Initial design for the Billing table

AC 1-5aDefining Fields

Defining a Field in Design View

AC 1-5bSpecifying the Primary Key

Specifying a Primary Key in Design View

AC 1-5cRenaming Fields in Design View

AC 1-5dSaving the Billing Table Structure

AC 1-6Closing a Table and Exiting Access

Saving a Database

AC 1-7Session 1.2 Visual Overview: The Create Tab Options

AC 1-8Entering Data into Tables

Opening a Database

Visit table records

AC 1-10Navigating a Datasheet

Navigation buttons

AC 1-12Creating a Simple Form

Saving Database Objects

AC 1-13aPrinting a Report

Printing a Report

AC 1-16Managing a Database

AC 1-16aCompacting and Repairing a Database

Compacting and Repairing a Database

Option 1

Low Cost Option

Download this past answer in few clicks

24.99 USD

PURCHASE SOLUTION

Option 2

Custom new solution created by our subject matter experts

GET A QUOTE

rated 5 stars

View Answer

Sitejabber (5.0)

BBC (5.0)

Trustpilot (4.9)

Google (5.0)

Related Questions