Homework answers / question archive / New Perspectives Access 2019 Module 2 Textbook Project Start Access and open the Billing table in Design view: 1

New Perspectives Access 2019 Module 2 Textbook Project Start Access and open the Billing table in Design view: 1

MS Access

Share With

---

New Perspectives Access 2019 Module 2 Textbook Project

Start Access and open the Billing table in Design view:

1. 1.

  Start Access and open the Lakewood database you created in the previous module.

Trouble? If the security warning is displayed below the ribbon, click the Enable Content button.

2. 2.

In the Navigation Pane, right-click the Billing table object and then click Design View on the shortcut menu.

3. 3.

Click the Shutter Bar Open/Close Button  to close the Navigation Pane.

4. 4.

Position the pointer in the row selector for row 1 (the InvoiceNum field) until the pointer changes to a right-pointing arrow . Placing it over the key symbol is fine.

5. 5.

Click the row selector to select the entire InvoiceNum row. See Figure 2-6.

Figure 2-6Billing table in Design view

 

 

 

The Billing table contains the same fields as when you created it in the previous module. Donna wants you to continue to modify the fields in the table as listed in Figure 2-5. The first step is to add properties to the InvoiceNum field.

Because you have already defined the Field Name, Data Type, and Description properties for the InvoiceNum field, you will use the Field Properties Pane to set the additional properties Donna wants. When you created the InvoiceNum field, you gave it a data type of Short Text. Figure 2-6 shows that the default number of characters for a field of the Short Text data type is 255. In addition, a Short Text field by default has no Caption property, which specifies how the name appears. Updating the Caption property will make the field name more readable. Donna wants you to update the Field Size and Caption properties.

To add properties to the InvoiceNum field:

1. 1.

Double-click the number 255 in the Field Size property box to select it, and then type 5.

You also need to set the Caption property for the field so that its name appears with a space, as “Invoice Num.”

2. 2.

Click the Caption property box, and then type Invoice Num (be sure to include a space between the two words). You have set properties for the InvoiceNum field. See Figure 2-7.

Figure 2-7InvoiceNum field properties updated

 

 

 

Next, you will add the properties Donna wants to the VisitID field. As with the InvoiceNum field, you will update the Field Size and Caption properties. Donna wants the field size of the VisitID field to be 4.

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. The field must be defined in the same way in both tables—that is, the field properties, including field size and data type, must match exactly. Later in this session, you’ll change the Field Size property for the VisitID field in the Visit table to 4 so that the field definition is the same in both tables.

To add properties to the VisitID field:

1. 1.

Position the pointer in the row selector for row 2 (the VisitID field) until the pointer changes to a right-pointing arrow .

2. 2.

Click the row selector to select the entire VisitID row.

3. 3.

Press F6 to move to the Field Properties pane. The current entry for the Field Size property, 255, is selected.

4. 4.

Type 4 to set the Field Size property. Next, you need to set the Caption property for this field.

5. 5.

Press TAB three times to position the insertion point in the Caption box, and then type Visit ID (be sure to include a space between the two words). You have finished modifying the VisitID field.

• The third field in the Billing table is the InvoiceAmt field, which has the Currency data type. Donna wants you to make a few modifications to the properties of this field.

In addition to adding a Caption field value, Donna wants to display the InvoiceAmt field values with two decimal places. The Decimal Places property specifies the number of decimal places that are displayed to the right of the decimal point.

To add properties to the InvoiceAmt field:

1. 1.

Position the pointer in the row selector for row 3 (the InvoiceAmt field) until the pointer changes to a right-pointing arrow .

2. 2.

Click the row selector to select the entire InvoiceAmt row.

3. 3.

 In the Field Properties pane, click the Decimal Places box to position the insertion point. An arrow appears on the right side of the Decimal Places box, which you can click to display a list of options.

4. 4.

Click the Decimal Places arrow, and then click 2 in the list to specify two decimal places for the InvoiceAmt field values.

5. 5.

Press TAB twice to position the insertion point in the Caption box, and then type Invoice Amt (be sure to include the space). The definition of the third field is now complete. Notice that the Format property is by default set to “Currency,” which formats the values with dollar signs, and is what Donna wants. See Figure 2-8.

Figure 2-8InvoiceAmt field properties updated

 

 

 

In these steps, you set the Decimal Places property for the InvoiceAmt field in Design view; however, it is also possible to change the number of decimal places for a field in Datasheet view. For fields of the Currency and Number data types, you can change the number of decimal places in either view. To change the number of decimal places in Datasheet view, you would first click a field to make it the active field. On the Table Tools Fields tab, in the Formatting group, use the Increase Decimals and Decrease Decimals buttons to add or remove decimal places in the field. When you do, Access makes the change in the corresponding Decimal Places property in Design view.

The fourth field in the Billing table is the InvoiceDate field. According to Donna’s design (Figure 2-5), the date values should be displayed in the format mm/dd/yyyy, which is a two-digit month, a two-digit day, and a four-digit year. In addition, she wants you to update the Caption property.

To add properties to the InvoiceDate field:

1. 1.

Position the pointer in the row selector for row 4 (the InvoiceDate field) until the pointer changes to a right-pointing arrow .

2. 2.

Click the row selector to select the entire InvoiceDate row.

Donna wants to display the values in the InvoiceDate field in a format showing the month, the day, and a four-digit year, as in the following example: 05/24/2021. You use the Format property to control the display of a field value.

3. 3.

In the Field Properties pane, click the right side of the Format box to display the list of predefined formats for Date/Time fields. See Figure 2-9.

Figure 2-9Displaying available formats for Date/Time fields

 

 

 

Trouble? If you see an arrow instead of a list of predefined formats, click the arrow to display the list.

As noted in the right side of the Field Properties pane, you can choose a predefined format or enter a custom format. Even though the Short Date format seems to match the format Donna wants, it displays only one digit for January to September. For example, it would display the month of May with only the digit “5”—as in 5/24/2021—instead of displaying the month with two digits, as in 05/24/2021.

Because none of the predefined formats matches the exact layout Donna wants for the InvoiceDate values, you need to create a custom date format. Figure 2-10 shows some of the symbols available for custom date and time formats.

Figure 2-10

Symbols for some custom date formats

Symbol	Description
/	date separator
d	day of the month in one or two numeric digits, as needed (1 to 31)
dd	day of the month in two numeric digits (01 to 31)
ddd	first three letters of the weekday (Sun to Sat)
dddd	full name of the weekday (Sunday to Saturday)
w	day of the week (1 to 7)
ww	week of the year (1 to 53)
m	month of the year in one or two numeric digits, as needed (1 to 12)
mm	month of the year in two numeric digits (01 to 12)
mmm	first three letters of the month (Jan to Dec)
mmmm	full name of the month (January to December)
yy	last two digits of the year (01 to 99)
yyyy	full year (0100 to 9999)

 

 

Donna wants to display the dates with a two-digit month (mm), a two-digit day (dd), and a four-digit year (yyyy).

4. 4.

Click the Format arrow to close the list of predefined formats, and then type mm/dd/yyyy in the Format box.

5. 5.

Press TAB twice to position the insertion point in the Caption box, and then type Invoice Date (be sure to include a space between the words). See Figure 2-11.

Figure 2-11Specifying the custom date format

 

 

 

The fifth and final field to modify in the Billing table is the InvoicePaid field. The only property Donna wants to update for the InvoicePaid field is the Caption property.

To add property to the InvoicePaid field:

1. 1.

Position the pointer in the row selector for row 5 (the InvoicePaid field) until the pointer changes to a right-pointing arrow .

2. 2.

Click the row selector to select the entire InvoicePaid row.

3. 3.

In the Field Properties pane, click the Caption box, and then type Invoice Paid (once again, be sure to include a space between the words).

You’ve finished adding properties to the fields for the Billing table. Normally after entering the fields and properties for a table in Design view, you would specify the primary key for the table; however, in the previous module you specified the primary key for the Billing table to be the InvoiceNum field.

Insight

Understanding the Importance of the Primary Key

Although Access does not require a table to have a primary key, including a primary key offers several advantages:

• A primary key uniquely identifies each record in a table.
• Access does not allow duplicate values in the primary key field. For example, if the Visit table already has a record with a VisitID value of 1549, Access prevents you from adding another record with this same value in the VisitID field. Preventing duplicate values ensures the uniqueness of the primary key field.
• When a primary key has been specified, Access forces you to enter a value for the primary key field in every record in the table. This is known as entity integrity. If you do not enter a value for a field, you have actually given the field a null value. You cannot give a null value to the primary key field because entity integrity prevents Access from accepting and processing that record.
• You can enter records in any order, but Access displays them by default in order of the primary key’s field values. If you enter records in no specific order, you will later be able to work with them in a more meaningful, primary key sequence.
• Access responds faster to your requests for specific records based on the primary key.

AC 2-4aSaving the Table Structure

You have already given the table a name, Billing; however, because you added many property values, you should save the changes you made to the table structure.

To save the Billing table changes:

1. 1.

On the Quick Access Toolbar, click the Save button .

Unlike the first time you saved the Billing table, you are not prompted for a name for the table. Because the name has already been assigned, Access updates the structure of the table using the same name.

AC 2-5Modifying the Structure of an Access Table

Even a well-designed table might need to be modified. Some changes that you can make to a table’s structure in Design view include changing the order of fields and adding new fields.

After meeting with her assistant, Taylor Bailey, and reviewing the structure of the Billing table, Donna asks you to make changes to the table. First, she wants to move the InvoiceAmt field so that it appears right before the InvoicePaid field. Then, she wants you to add a new Short Text field named InvoiceItem to include information about what the invoice is for, such as office visits, lab work, and so on. Donna would like to insert the InvoiceItem field between the InvoiceAmt and InvoicePaid fields.

 

AC 2-5aMoving a Field in Design View

To move a field, you use the mouse to drag it to a new location in the Table Design grid. Although you can move a field in Datasheet view by dragging its column heading to a new location, doing so rearranges only the display of the table’s fields; the table structure is not changed. To move a field permanently, you must move the field in Design view.

Next, you’ll move the InvoiceAmt field so that it appears before the InvoicePaid field in the Billing table.

To move the InvoiceAmt field:

1. 1.

Position the pointer on the row selector for the InvoiceAmt field until the pointer changes to a right-pointing arrow .

2. 2.

Click the row selector to select the entire InvoiceAmt row.

3. 3.

Place the pointer on the row selector for the InvoiceAmt field until the pointer changes to a selection pointer , press and hold the mouse button, and then drag to the row selector for the InvoicePaid field. As you drag, the pointer changes to a move pointer . See Figure 2-12.

Figure 2-12Moving the InvoiceAmt field in the table structure

 

 

 

4. 4.

Release the mouse button. The InvoiceAmt field now appears between the InvoiceDate and InvoicePaid fields in the table structure.

Trouble? If the InvoiceAmt field did not move, repeat Steps 1, 2, 3, and 4, making sure you hold down the mouse button while dragging.

AC 2-5bAdding a Field in Design View

To add a new field between existing fields, you must insert a row. You begin by selecting the row below where you want to insert the new field.

Reference

Adding a Field Between Two Existing Fields

• In the Table window in Design view, select the row below where you want to insert the new field.
• In the Tools group on the Table Tools Design tab, click the Insert Rows button.
• Define the new field by entering the field name, data type, optional description, and any property specifications.

Next, you need to add the InvoiceItem field to the Billing table structure between the InvoiceAmt and InvoicePaid fields.

To add the InvoiceItem field to the Billing table:

1. 1.

Click the InvoicePaid Field Name box. You need to establish this field as the current field to insert the row for the new field above this field.

2. 2.

On the Table Tools Design tab, in the Tools group, click Insert Rows. A new, blank row is added between the InvoiceAmt and InvoicePaid fields. The insertion point is positioned in the Field Name box for the new row, ready for you to type the name for the new field. See Figure 2-13.

Figure 2-13Table structure after inserting a row

 

 

 

Trouble? If you selected the InvoicePaid field’s row selector and then inserted the new row, you need to click the new row’s Field Name box to position the insertion point in it.

You’ll define the InvoiceItem field in the new row of the Billing table. This field will be a Short Text field with a field size of 40. You also need to set the Caption property to include a space between the words in the field name.

3. 3.

Type InvoiceItem, press TAB to move to the Data Type property, and then press TAB again to accept the default Short Text data type.

4. 4.

Press F6 to select the default field size in the Field Size box, and then type 40.

5. 5.

Press TAB three times to position the insertion point in the Caption box, and then type Invoice Item. The definition of the new field is complete. See Figure 2-14.

Figure 2-14InvoiceItem field added to the Billing table

 

 

 

6. 6.

On the Quick Access Toolbar, click the Save button  to save the changes to the Billing table structure.

7. 7.

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

 

AC 2-6Modifying Field Properties

With the Billing table design complete, you can now go back and modify the properties of the fields in the Visit table you created in the previous module, as necessary. You can make some changes to properties in Datasheet view; for others, you’ll work in Design view.

AC 2-6aChanging the Format Property in Datasheet View

The Formatting group on the Table Tools Fields tab in Datasheet view allows you to modify some formatting properties for certain field types. When you format a field, you change the way data is displayed, but not the actual values stored in the table.

Next, you’ll check the properties of the VisitDate field in the Visit table to see if any changes would improve the display of the date values.

To modify the VisitDate field’s Format property:

1. 1.

In the Navigation Pane, click the Shutter Bar Open/Close Button  to open the pane. Notice that the Billing table is listed above the Visit table in the Tables section. By default, objects are listed in alphabetical order in the Navigation Pane.

2. 2.

Double-click Visit to open the Visit table in Datasheet view.

3. 3.

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

4. 4.

Position the pointer in the row selector for the first record (VisitID 1450) until the pointer changes to a right-pointing arrow .

5. 5.

Click the row selector to select the entire first record. See Figure 2-15.

Figure 2-15Visit table datasheet

 

 

 

The values in the three Short Text fields—VisitID, PatientID, and Reason—appear left-aligned within their boxes, and the values in the Date/Time field (VisitDate) appear right-aligned. In Access, values for Short Text fields are left-aligned, and values for Number, Date/Time, and Currency fields are right-aligned. The WalkIn field is a Yes/No field, so its values appear in check boxes that are centered within the column.

6. 6.

On the ribbon, click the Table Tools Fields tab.

7. 7.

Click the first field value in the VisitDate column. The Data Type option shows that this field is a Date/Time field.

By default, Access assigns the General Date format to Date/Time fields. Note the Format box in the Formatting group, which you use to set the Format property (similar to how you set the Format property in the Field Properties pane in Design view). Even though the Format box is empty, the VisitDate field has the General Date format applied to it. The General Date format includes settings for date or time values, or a combination of date and time values. However, Donna wants to display only date values in the VisitDate field, so she asks you to specify the Short Date format for the field.

8. 8.

In the Formatting group, click the Format arrow, and then click Short Date. See Figure 2-16.

Figure 2-16VisitDate field after modifying the format

 

 

 

 Although no change is apparent in the datasheet—the VisitDate field values already appear with the Short Date setting (for example, 11/3/2020), as part of the default General Date format—the field now has the Short Date format applied to it. This ensures that only date field values, and not time or date/time values, are allowed in the field.

When you change a field’s property in Design view, you may see the Property Update Options button . This button appears when you modify a property for a field included in a query, form, or report in the database and asks if you want to update the related properties of the field in the other objects. For example, if the Lakewood database included a form or report that contained the PatientID field, and you modified a property of the PatientID field in the Patient table, you could choose to propagate, or update, the modified property by clicking the Property Update Options button, and then choosing the option to make the update everywhere the field is used. You are not required to update the related objects; however, in most cases, it is a good idea to perform the update.

AC 2-6bChanging Properties in Design View

Recall that each of the Short Text fields in the Visit table—VisitID, PatientID, and Reason—still has the default field size of 255, which is too large for the data contained in these fields. Also, the VisitID and PatientID fields need descriptions to identify them as the primary and foreign keys, respectively, in the table. Finally, each of these fields needs a caption to include a space between the words in the field name or to make the name more descriptive. You can make all of these property changes more easily in Design view.

To modify the Field Size, Description, and Caption field properties:

1. 1.

 On the Table Tools Fields tab, in the Views group, click the View button. The table is displayed in Design view with the VisitID field selected. You need to enter a Description property value for this field, the primary key in the table, and change its Field Size property to 4 because each visit number at Lakewood Community Health Services consists of four digits.

Trouble? If you clicked the arrow on the View button, a menu appears. Choose Design View from the menu.

2. 2.

Press TAB twice to position the insertion point in the Description (Optional) box, and then type Primary key.

3. 3.

Press F6 to move to and select the default setting of 255 in the Field Size box in the Field Properties pane, and then type 4. Next, you need to set the Caption property for this field.

4. 4.

Press TAB three times to position the insertion point in the Caption box, and then type Visit ID.

5. 5.

Click the PatientID Field Name box, press TAB twice to position the insertion point in the Description (Optional) box, and then type Foreign key.

6. 6.

Press F6 to move to and select the default setting of 255 in the Field Size box in the Field Properties pane, and then type 5.

7. 7.

Press TAB three times to position the insertion point in the Caption box, and then type Patient ID.

8. 8.

Click the VisitDate Field Name box, click the Caption box, and then type Date of Visit.

For the Reason field, you will set the Field Size property to 60. This size can accommodate the longer values in the Reason field. You’ll also set this field’s Caption property to provide a more descriptive name.

9. 9.

Click the Reason Field Name box, press F6, type 60, press TAB three times to position the insertion point in the Caption box, and then type Reason/Diagnosis.

Finally, you’ll set the Caption property for the WalkIn field.

10. 10.

Click the WalkIn Field Name box, click the Caption box, and then type Walk-in?. See Figure 2-17.

Figure 2-17Visit table after modifying field properties

 

 

 

The WalkIn field’s Default Value property is automatically set to “No,” which means the check box for this field will be empty for each new record. This is the default for this property for any Yes/No field. You can set the Default Value property for other types of fields to make data entry easier. You’ll learn more about setting this property in the next session.

The changes to the Visit table’s properties are now complete, so you can save the table and view the results of your changes in Datasheet view.

To save and view the modified Visit table:

1. 1.

On the Quick Access Toolbar, click the Save button  to save the modified table. A dialog box opens informing you that some data may be lost because you decreased the field sizes. Because all of the values in the VisitID, PatientID, and Reason fields contain the same number of or fewer characters than the new Field Size properties you set for each field, you can ignore this message.

2. 2.

Click the Yes button.

3. 3.

On the Table Tools Design tab, in the Views group, click the View button to display the Visit table in Datasheet view. Each column (field) heading now displays the text you specified in the Caption property for that field. See Figure 2-18.

Figure 2-18Modified Visit table in Datasheet view

 

 

 

4. 4.

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

5. 5.

If you are not continuing to Session 2.2, click the File tab, and then click Close to close the Lakewood database.

You have modified the design of the Billing table. In the next session, you’ll add records to the Billing table and create the Patient table in the Lakewood database.

AC 2-8Adding Records to a New Table

Before you can begin to define the table relationships illustrated in the Session 2.2 Visual Overview, you need to finish creating the tables in the Lakewood database.

The Billing table design is complete. Now, Donna would like you to add records to the table so it contains the invoice data for Lakewood Community Health Services. As you learned earlier, you add records to a table in Datasheet view by typing the field values in the rows below the column headings for the fields. You’ll begin by entering the records shown in Figure 2-19.

Figure 2-19

Records to add to the Billing table

Invoice Num	Visit ID	Invoice Date	Invoice Amt	Invoice Item	Invoice Paid
26501	1450	10/27/2020	$125.00	Office visit	Yes
26589	1495	12/28/2020	$125.00	Office visit	No
26655	1530	01/27/2021	$50.00	Lab work	Yes
26767	1598	03/26/2021	$50.00	Lab work	No

 

 

To add the first record to the Billing table:

1. 1.

If you took a break after the previous session, make sure the Lakewood database is open and the Navigation Pane is open.

2. 2.

In the Tables section of the Navigation Pane, double-click Billing to open the Billing table in Datasheet view.

3. 3.

Close the Navigation Pane, and then use the column resizing pointer  to resize columns, as necessary, so that the field names are completely visible.

4. 4.

 In the Invoice Num column, type 26501, press TAB, type 1450 in the Visit ID column, and then press TAB.

5. 5.

Type 10/27/2020 and then press TAB.

Next, you need to enter the invoice amount for the first record. This is a Currency field with the Currency format and two decimal places specified. Because of the field’s properties, you do not need to type the dollar sign, comma, or zeroes for the decimal places; Access displays these items automatically.

6. 6.

Type 125 and then press TAB. The value is displayed as “$125.00.”

7. 7.

In the Invoice Item column, type Office visit, and then press TAB.

The last field in the table, InvoicePaid, is a Yes/No field. Recall that the default value for any Yes/No field is “No”; therefore, the check box is initially empty. For the record you are entering in the Billing table, the invoice has been paid, so you need to insert a checkmark in the check box in the Invoice Paid column.

8. 8.

Press SPACEBAR to insert a checkmark, and then press TAB. The values for the first record are entered. See Figure 2-20.

Figure 2-20First record entered in the Billing table

 

 

 

Now you can add the remaining three records. As you do, you’ll learn a keyboard shortcut for inserting the value from the same field in the previous record. A keyboard shortcut is a key or combination of keys you press to complete an action more efficiently.

To add the next three records to the Billing table:

1. 1.

Refer to Figure 2-19 and enter the values in the second record’s Invoice Num, Visit ID, and Invoice Date columns.

Notice that the value in the second record’s Invoice Amt column is $125.00. This value is the exact same value as in the first record. You can quickly insert the value from the same column in the previous record using the CTRL+’ (apostrophe) keyboard shortcut. To use this shortcut, you press and hold CTRL, press the ’ key once, and then release both keys. (The plus sign in the keyboard shortcut indicates you are pressing two keys at once; you do not press the + key.)

2. 2.

With the insertion point in the Invoice Amt column, press CTRL+’. The value “$125.00” is inserted in the Invoice Amt column for the second record.

3. 3.

Press TAB to move to the Invoice Item column. Again, the value you need to enter in this column—Office visit—is the same as the value for this column in the previous record. So, you can use the keyboard shortcut again.

4. 4.

With the insertion point in the Invoice Item column, press CTRL+’. Access inserts the value “Office visit” in the Invoice Item column for the second record.

5. 5.

Press TAB to move to the Invoice Paid column, and then press TAB to leave the Invoice Paid check box unchecked to indicate the invoice has not been paid. The second record is entered in the Billing table.

6. 6.

Refer to Figure 2-19 to enter the values for the third and fourth records, using CTRL+’ to enter the value in the fourth record’s Invoice Amt and Invoice Item columns. Your table should look like the one in Figure 2-21.

Figure 2-21Billing table with four records entered

 

 

 

To finish entering records in the Billing table, you’ll use a method that allows you to import the data.

AC 2-9Importing Data from an Excel Worksheet

Often, the data you want to add to an Access table is stored in another file, such as a Word document or an Excel workbook. You can add the data from other files to Access in different ways. For example, you can copy and paste the data from an open file, or you can import the data, which is a process that allows you to copy the data from a source without having to open the source file.

Insight

Caption Property Values and the Import Process

When you import data from an Excel worksheet into an Access table, the import process does not consider any Caption property values set for the fields in the table. For example, the Access table could have fields such as InvoiceDate and InvoiceAmt with Caption property values of Invoice Date and Invoice Amt, respectively. If the Excel worksheet you are importing has the column headings Invoice Date and Invoice Amt, you might think that the data matches and you can proceed with the import. However, if the underlying field names in the Access table do not match the Excel worksheet column headings exactly, the import process will fail. It is a good idea to double-check to make sure that the actual Access field names—and not just the column headings displayed in a table datasheet (as specified by the Caption property)—match the Excel worksheet column headings. If there are differences, you can change the column headings in the Excel worksheet to match the Access table field names before you import the date, ensuring that the process will work correctly.

Donna had been using Excel to track invoice data for Lakewood Community Health Services and already created a workbook, named Support_AC_2_Invoices.xlsx, containing this data. You’ll import the Billing worksheet from this Excel workbook into your Billing table to complete the entry of data in the table. To use the import method, the columns in the Excel worksheet must match the names and data types of the fields in the Access table.

The Billing worksheet contains the following columns: InvoiceNum, VisitID, InvoiceDate, InvoiceAmt, InvoiceItem, and InvoicePaid. These column headings match the field names in the Billing table exactly, so you can import the data. Before you import data into a table, you need to close the table.

To import the Excel data into the Billing table:

1. 1.

Click the Close ‘Billing’ button  on the object tab to close the Billing table, and then click the Yes button in the dialog box asking if you want to save the changes to the table layout. This dialog box opens because you resized the table columns.

2. 2.

On the ribbon, click the External Data tab.

3. 3.

In the Import & Link group, click the New Data Source button.

4. 4.

In the New Data Source list, click the From File option. You may also point to the option.

5. 5.

In the From File list, click Excel. The Get External Data - Excel Spreadsheet dialog box opens. See Figure 2-22.

Figure 2-22Get External Data – Excel Spreadsheet dialog box

 

 

 

The dialog box provides options for importing the entire worksheet as a new table in the current database, adding the data from the worksheet to an existing table, or linking the data in the worksheet to the table. You need to add, or append, the worksheet data to the Billing table.

6. 6.

Click the Browse button. The File Open dialog box opens. The Excel workbook file is named “Support_AC_2_Invoices.xlsx” and is located in the Access1 > Module folder provided with your Data Files.

7. 7.

Navigate to the Access1 > Module folder, where your Data Files are stored, and then double-click the Support_AC_2_Invoices.xlsx Excel file. You return to the dialog box.

8. 8.

Click the Append a copy of the records to the table option button. The box to the right of this option becomes active and displays the Billing table name because it is the first table listed in the Navigation Pane.

9. 9.

Click OK. The first Import Spreadsheet Wizard dialog box opens. The dialog box confirms that the first row of the worksheet you are importing contains column headings. The bottom section of the dialog box displays some of the data contained in the worksheet. See Figure 2-23.

Figure 2-23First Import Spreadsheet Wizard dialog box

 

 

 

10. 10.

Click Next. The second, and final, Import Spreadsheet Wizard dialog box opens. The Import to Table box shows that the data from the spreadsheet will be imported into the Billing table.

11. 11.

Click Finish. A dialog box opens asking if you want to save the import steps. If you needed to repeat this same import procedure many times, it would be a good idea to save the steps for the procedure. However, you don’t need to save these steps because you are importing the data only one time. After the data is in the Billing table, Donna will no longer use Excel to track invoice data.

12. 12.

Click Close in the dialog box to close it without saving the steps.

The data from the Billing worksheet in the Support_AC_2_Invoices.xlsx workbook has been added to the Billing table. Next, you’ll open the table to view the new records.

To open the Billing table and view the imported data:

1. 1.

Open the Navigation Pane, and then double-click Billing in the Tables section to open the table in Datasheet view.

2. 2.

Resize the Invoice Item column to its best fit, scrolling the worksheet and resizing, as necessary.

3. 3.

Press CTRL+HOME to scroll to the top of the datasheet. The table now contains a total of 205 records—the four records you entered plus 201 records imported from the Invoices worksheet. The records are displayed in primary key order by the values in the Invoice Num column. See Figure 2-24.

Figure 2-24Billing table after importing data from Excel

 

 

 

4. 4.

Save and close the Billing table, and then close the Navigation Pane.

Two of the tables—Visit and Billing—are now complete. According to Donna’s plan for the Lakewood database, you still need to create the Patient table. You’ll use a different method to create this table.

Insight

Options for Importing Data from a Spreadsheet

Because you already created and added the initial four records to the Billing table, you chose the option to append the additional records from the Invoices worksheet to the Billing table. The Get External Data – Excel Spreadsheet dialog box also has two other options (see Figure 2-22).

The first option is to import the source data into a new table in the current database. If the Invoices worksheet contained all the records to add to the Billing table, you could have chosen this option to import all the records. If the specified table does not exist, Access creates it. If the specified table already exists, Access might overwrite its contents with the imported data. Changes made to the source data would not be reflected in the database.

The second option is to link to the data source by creating a linked table. With this option, Access creates a table that maintains a link to the source data in Excel. Changes made to the source data in Excel will be reflected in the linked table. However, the source data cannot be changed within Access. Because Donna wanted to move the data from the Excel worksheet into Access and no longer use Excel, you did not choose this option.

AC 2-10Creating a Table by Importing an Existing Table or Table Structure

If another Access database contains a table—or only the design, or structure, of a table—that you want to include in your database, you can import the table and any records it contains or import only the table structure into your database. To create the new Patient table per Donna’s plan shown in Figure 2-2, you will import a table structure from a different Access database to create the Patient table.

Donna documented the design for the new Patient table by listing each field’s name and data type, as well as any applicable field size, description, and caption property values, as shown in Figure 2-25. Note that each field in the Patient table, except BirthDate, will be a Short Text field, and the PatientID field will be the table’s primary key.

Figure 2-25

Design for the Patient table

Field Name	Data Type	Field Size	Description	Caption
PatientID	Short Text	5	Primary key	Patient ID
LastName	Short Text	25		Last Name
FirstName	Short Text	20		First Name
BirthDate	Date/Time			Date of Birth
BirthDate	Date/Time			Date of Birth
Phone	Short Text	14
Address	Short Text	35
City	Short Text	25
State	Short Text	2
Zip	Short Text	10
Email	Short Text	50

 

 

Donna’s assistant Taylor already created an Access database containing a Patient table design; however, she hasn’t entered any records into the table. After reviewing the table design, both Taylor and Donna agree that it contains some of the fields they want, but that some changes are needed. You will import the table structure in Taylor’s database to create the Patient table in the Lakewood database, and later in this session, you will modify the imported table to produce the final table structure according to Donna’s design.

To create the Patient table by importing the structure of another table:

1. 1.

Make sure the External Data tab is the active tab on the ribbon.

2. 2.

In the Import & Link group, click the New Data Source button.

3. 3.

In the New Data Source list, click the From Database option. You may also point to the option.

4. 4.

In the From Database list, click Access. The Get External Data – Access Database dialog box opens. This dialog box is similar to the one you used earlier when importing the Excel spreadsheet.

5. 5.

Click the Browse button. The File Open dialog box opens. The Access database file from which you need to import the table structure is named “Support_AC_2_Taylor.accdb” and is located in the Access1 > Module folder provided with your Data Files.

6. 6.

Navigate to the Access1 > Module folder, where your Data Files are stored, and then double-click the Support_AC_2_Taylor.accdb database file. You return to the dialog box.

7. 7.

Make sure the Import tables, queries, forms, reports, macros, and modules into the current database option button is selected, and then click OK. The Import Objects dialog box opens. The dialog box contains tabs for importing all types of Access database objects—tables, queries, forms, and so on. The Tables tab is the current tab.

8. 8.

Click the Options button in the dialog box to see all the options for importing tables. See Figure 2-26.

Figure 2-26Import Objects dialog box

 

 

 

9. 9.

On the Tables tab, click Patient to select this table.

10. 10.

In the Import Tables section of the dialog box, click the Definition Only option button, and then click OK. Access creates the Patient table in the Lakewood database using the structure of the Patient table in the Support_AC_2_Taylor.accdb database, and opens a dialog box asking if you want to save the import steps.

11. 11.

Click Close to close the dialog box without saving the import steps.

12. 12.

Open the Navigation Pane, double-click Patient in the Tables section to open the table, and then close the Navigation Pane. The Patient table opens in Datasheet view. The table contains no records. See Figure 2-27.

Figure 2-27Imported Patient table in Datasheet view

 

 

 

The table structure you imported contains some of the fields Donna wants, but not all (see Figure 2-25); it also contains some fields Donna does not want in the Patient table. You can add the missing fields using the Data Type gallery.

AC 2-11Adding Fields to a Table Using the Data Type Gallery

The Data Type gallery, available from the More Fields button in the Add & Delete group on the Table Tools Fields tab, allows you to add a group of related fields to a table at the same time, rather than adding each field to the table individually. The group of fields you add is called a Quick Start selection. For example, the Address Quick Start selection adds a collection of fields related to an address, such as Address, City, State, and so on, to the table at one time. When you use a Quick Start selection, the fields you add already have properties set. However, you need to review and possibly modify the properties to ensure the fields match your design needs for the database.

Next, you’ll use the Data Type gallery to add the missing fields to the Patient table.

To add fields to the Patient table using the Data Type gallery:

1. 1.

On the ribbon, click the Table Tools Fields tab. Note the More Fields button in the Add & Delete group; you use this button to display the Data Type gallery. Before inserting fields from the Data Type gallery, you need to place the insertion point in the field to the right of where you want to insert the new fields. According to Donna’s design, the Address field should come after the Phone field, so you need to make the next field, Email, the active field.

2. 2.

 Click the first row in the Email field to make it the active field.

3. 3.

In the Add & Delete group, click the More Fields button. The Data Type gallery opens and displays options for types of fields you can add to your table.

4. 4.

Scroll down the gallery until the Quick Start section is visible. See Figure 2-28.

Figure 2-28Patient table with the Data Type gallery displayed

 

 

 

The Quick Start section provides options that add related fields to the table at one time. The new fields will be inserted to the right of the current field.

5. 5.

In the Quick Start section, click Address. Five fields are added to the table: Address, City, State Province, ZIP Postal, and Country Region. See Figure 2-29.

Figure 2-29Patient table after adding fields from the Data Type gallery

 

AC 2-12aDeleting Fields from a Table Structure

After you’ve created a table, you might need to delete one or more fields. When you delete a field, you also delete all the values for that field from the table. So, before you delete a field, make sure that you want to do so and that you choose the correct field to delete. You can delete fields in either Datasheet view or Design view.

Reference

Deleting a Field from a Table Structure

• In Datasheet view, click anywhere in the column for the field you want to delete.
• On the Table Tools Fields tab in the Add & Delete group, click the Delete button.

or

• In Design view, click the Field Name box for the field you want to delete.
• On the Table Tools Design tab in the Tools group, click the Delete Rows button.

The Address Quick Start selection added a field named “Country Region” to the Patient table. Donna doesn’t need a field to store country data because all of the patients of Lakewood Community Health Services are located in the United States. You’ll begin to modify the Patient table structure by deleting the Country Region field.

To delete the Country Region field from the table in Datasheet view:

1. 1.

Click the first row in the Country Region field (if necessary).

2. 2.

On the Table Tools Fields tab, in the Add & Delete group, click the Delete button. The Country Region field is removed and the first field, PatientID, is now the active field.

You can also delete fields from a table structure in Design view. You’ll switch to Design view to delete the other unnecessary fields.

To delete the fields in Design view:

1. 1.

On the Table Tools Fields tab, in the Views group, click the View button. The Patient table opens in Design view. See Figure 2-30.

Figure 2-30Patient table in Design view

 

 

 

Trouble? If you clicked the arrow on the View button, a menu appears. Choose Design View from the menu.

2. 2.

Click the Title Field Name box to make it the current field.

3. 3.

On the Table Tools Design tab, in the Tools group, click the Delete Rows button. The Title field is removed from the Patient table structure. You’ll delete the Fax, County, and Notes fields next. Instead of deleting these fields individually, you’ll select and delete them at the same time.

4. 4.

On the row selector for the Fax field, press and hold the mouse button and then drag the mouse to select the County and Notes fields.

5. 5.

Release the mouse button. The rows for the three fields are outlined in red, indicating all three fields are selected.

You may not be able to see the Notes field; however, you can scroll down to view the selection.

6. 6.

In the Tools group, click the Delete Rows button. See Figure 2-31.

Figure 2-31Patient table after deleting fields

 

AC 2-12bRenaming Fields in Design View

To match Donna’s design for the Patient table, you need to rename some of the fields. You already have renamed the default primary key field (ID) in Datasheet view. You can also rename fields in Design view by editing the names in the Table Design grid.

To rename the fields in Design view:

1. 1.

Click to position the insertion point to the right of the text StateProvince in the eighth row’s Field Name box, and then press BACKSPACE eight times to delete the word “Province.” The name of the eighth field is now State.

You can also select an entire field name and then type new text to replace it.

2. 2.

In the ninth row’s Field Name box, drag to select the text ZIPPostal, and then type Zip. The text you type replaces the original text. See Figure 2-32.

Figure 2-32Patient table after renaming fields

 

 

 

Besides renaming fields, you can rename Access objects such as tables, queries, forms, and reports. To rename a table, for example, right-click the table object in the Navigation Pane, and then click Rename on the shortcut menu. Type the new name for the table and then press ENTER. By default, Access changes the table name in any other objects that reference the table.

AC 2-12cChanging the Data Type for a Field in Design View

In the table structure you imported earlier, you used an option in Datasheet view to change a field’s data type. You can also change the data type for a field in Design view. According to Donna’s plan, all the fields in the Patient table should be Short Text fields, except for BirthDate.

To change the data type of the Phone field in Design view:

1. 1.

Click the right side of the Data Type box for the Phone field to display the list of data types.

2. 2.

Click Short Text in the list. The Phone field is now a Short Text field. By default, the Field Size property is set to 255. According to Donna’s plan, the Phone field should have a Field Size property of 14. You’ll make this change next.

3. 3.

Press F6 to move to and select the default Field Size property, and then type 14.

Each of the remaining fields you added using the Address Quick Start selection—Address, City, State, and Zip—also has the default field size of 255. You need to change the Field Size property for these fields to match Donna’s design. You’ll also delete any Caption property values for these fields because the field names match how Donna wants them displayed, so captions are unnecessary.

To change the Field Size and Caption properties for the fields:

1. 1.

Click the Address Field Name box to make it the current field.

2. 2.

Press F6 to move to and select the default Field Size property, and then type 35. Because the Caption property setting for this field is the same as the field name, the field doesn’t need a caption, so you can delete this value.

3. 3.

Press TAB three times to select Address in the Caption box, and then press Delete. The Caption property value is removed.

4. 4.

Repeat Steps 1, 2, and 3 for the City field to change the Field Size property to 25 and delete its Caption property value.

5. 5.

Change the Field Size property for the State field to 2, and then delete its Caption property value.

6. 6.

Change the Field Size property for the Zip field to 10, and then delete its Caption property value.

7. 7.

On the Quick Access Toolbar, click the Save button  to save your changes to the Patient table.

Finally, Donna would like you to set the Description property for the PatientID field and the Caption property for the PatientID, LastName, and FirstName fields. You’ll make these changes now.

To enter the Description and Caption property values:

1. 1.

Click the Description (Optional) box for the PatientID field, and then type Primary key.

2. 2.

In the Field Properties pane, click the Caption box.

3. 3.

In the Caption box for the PatientID field, type Patient ID.

4. 4.

Click the LastName Field Name box to make it the current field, click the Caption box, and then type Last Name.

5. 5.

Click the FirstName Field Name box to make it the current field, click the Caption box, and then type First Name.

6. 6.

Click the BirthDate Field Name box to make it the current field, click the Caption box, and then type Date of Birth. See Figure 2-33.

Figure 2-33Patient table after entering descriptions and captions

 

 

 

7. 7.

On the Quick Access Toolbar, click the Save button  to save your changes to the Patient table.

8. 8.

On the Table Tools Design tab, in the Views group, click the View button to display the table in Datasheet view.

9. 9.

Resize each column to its best fit, and then click in the first row for the Patient ID column. See Figure 2-34.

Figure 2-34Modified Patient table in Datasheet view

 

 

 

Donna mentions that data entry would be easier if the State field had the value of “GA” for each new record added to the table, because all of the patients live in Georgia. You can accomplish this by setting the Default Value property for the field.

AC 2-13Setting the Default Value Property for a Field

The Default Value property for a field specifies what value will appear, by default, for the field in each new record you add to a table.

Because all the patients at Lakewood Community Health Services live in Georgia, you’ll specify a default value of “GA” for the State field in the Patient table. With this setting, each new record in the Patient table will have the correct State field value entered automatically.

To set the Default Value property for the State field:

1. 1.

In the Views group, click the View button to display the Patient table in Design view.

2. 2.

Click the State Field Name box to make it the current field.

3. 3.

In the Field Properties pane, click the Default Value box, type GA, and then press TAB. See Figure 2-35.

Figure 2-35Specifying the Default Value property for the State field

 

 

 

Note that a text entry in the Default Value property must be enclosed within quotation marks. If you do not type the quotation marks, Access adds them for you. However, for some entries, such as those that include punctuation, you would receive an error message indicating invalid syntax if you omitted the quotation marks. In such cases, you have to enter the quotation marks yourself.

4. 4.

On the Quick Access Toolbar, click the Save button  to save your changes to the Patient table.

5. 5.

 Display the table in Datasheet view. Note that the State field for the first row now displays the default value “GA” as specified by the Default Value property. Each new record entered in the table will automatically have this State field value entered.

With the Patient table design set, you can now enter records in it. You’ll begin by entering two records in the datasheet, and then use a different method to add the remaining records.

To add two records to the Patient table:

1. 1.

Enter the following values in the columns in the first record; press TAB to move past the default State field value:

Patient ID = 13250

Last Name = [student’s last name]

First Name = [student’s first name]

Date of Birth = 4/9/1995

Phone = 404-555-8445

Address = 123 Harbor Rd

City = Atlanta

State = GA

Zip = 30303

Email = student@example.com

2. 2.

Enter the following values in the columns in the second record:

Patient ID = 13287

Last Name = Perez

First Name = Luis

Date of Birth = 11/30/1988

Phone = 404-555-5903

Address = 78 Wynborne Dr

City = Decatur

State = GA

Zip = 30030

Email = l.perez12@example.com

3. 3.

Resize columns to their best fit, as necessary, and then save and close the Patient table.

Before Donna decided to store data using Access, Taylor managed the patient data for the clinic in a different system. She exported that data into a text file and now asks you to import it into the new Patient table. You can import the data contained in this text file to add the remaining records to the Patient table.

AC 2-14Adding Data to a Table by Importing a Text File

So far, you’ve learned how to add data to an Access table by importing an Excel spreadsheet, and you’ve created a new table by importing the structure of an existing table. You can also import data contained in text files.

To finish entering records in the Patient table, you’ll import the data contained in Taylor’s text file. The file is named “Support_AC_2_Patient.txt” and is located in the Access1 > Module folder provided with your Data Files.

To import the data contained in the Patient text file:

1. 1.

On the ribbon, click the External Data tab.

2. 2.

In the Import & Link group, click the New Data Source button.

3. 3.

In the New Data Source list, click the From File option. You may also point to the option.

4. 4.

In the From File list, click Text File. The Get External Data – Text File dialog box opens. This dialog box is similar to the one you used earlier when importing the Excel spreadsheet.

5. 5.

Click the Browse button. The File Open dialog box opens.

6. 6.

Navigate to the Access1 > Module folder, where your Data Files are stored, and then double-click the Support_AC_2_Patient.txt file. You return to the Get External Data – Text File dialog box.

7. 7.

Click the Append a copy of the records to the table option button. The box to the right of this option becomes active. Next, you need to select the table to which you want to add the data.

8. 8.

Click the arrow in the box, and then click Patient.

9. 9.

Click OK. The first Import Text Wizard dialog box opens. The dialog box indicates that the data to import is in a delimited format. In a delimited text file, fields of data are separated by a character such as a comma or a tab. In this case, the dialog box shows that data is separated by the comma character in the text file.

10. 10.

Make sure the Delimited option button is selected in the dialog box, and then click Next. The second Import Text Wizard dialog box opens. See Figure 2-36.

Figure 2-36Second Import Text Wizard dialog box

 

 

 

This dialog box asks you to confirm the delimiter character that separates the fields in the text file you’re importing. Access detects that the comma character is used in the Patient text file and selects this option. The bottom area of the dialog box provides a preview of the data you’re importing.

11. 11.

Make sure the Comma option button is selected, and then click Next. The third and final Import Text Wizard dialog box opens. The Import to Table box shows that the data will be imported into the Patient table.

12. 12.

Click Finish, and then click Close in the dialog box that opens to close it without saving the import steps.

Donna asks you to open the Patient table in Datasheet view so she can see the results of importing the text file.

To view the Patient table datasheet:

1. 1.

Open the Navigation Pane (if necessary), and then double-click Patient to open the Patient table in Datasheet view. The Patient table contains a total of 51 records.

2. 2.

Close the Navigation Pane, and then resize columns to their best fit, scrolling the table datasheet as necessary, so that all field values are displayed. Scroll back to display the first fields in the table, and then click the first row’s Patient ID field, if necessary. See Figure 2-37.

Figure 2-37Patient table after importing data from the text file

 

 

 

3. 3.

Save and close the Patient table, and then open the Navigation Pane.

The Lakewood database now contains three tables—Billing, Patient, and Visit—and the tables contain all the necessary records. Your final task is to complete the database design by defining the necessary relationship between its tables.

AC 2-15Defining Table Relationships

One of the most powerful features of a relational database management system is its ability to define relationships between tables. You use a common field to relate one table to another. The process of relating tables is often called performing a join. When you join tables that have a common field, you can extract data from them as if they were one larger table. For example, you can join the Patient and Visit tables by using the PatientID field in both tables as the common field. Then you can use a query, form, or report to extract selected data from each table, even though the data is contained in two separate tables, as shown in Figure 2-38. The PatientVisits query shown in Figure 2-38 includes the PatientID, LastName, and FirstName fields from the Patient table, and the VisitDate and Reason fields from the Visit table. The joining of records is based on the common field of PatientID. The Patient and Visit tables have a type of relationship called a one-to-many relationship.

Figure 2-38One-to-many relationship and sample query

 

AC 2-15aOne-to-Many Relationships

As shown in the Session 2.2 Visual Overview, two tables have a one-to-many relationship when one record in the first table matches zero, one, or many records in the second table, and when one record in the second table matches at most one record in the first table. For example, as shown in Figure 2-38, patient 13256 has two visits in the Visit table. Other patients have one or more visits. Every visit has a single matching patient.

In Access, the two tables that form a relationship are referred to as the primary table and the related table. The primary table is the “one” table in a one-to-many relationship; in Figure 2-38, the Patient table is the primary table because there is only one patient for each visit. The related table is the “many” table; in Figure 2-38, the Visit table is the related table because a patient can have zero, one, or many visits.

Because related data is stored in two tables, inconsistencies between the tables can occur. Referring to Figure 2-38, consider the following three scenarios:

• Donna adds a record to the Visit table for a new patient, Edgar Faust, using Patient ID 13500. She did not first add the new patient’s information to the Patient table, so this visit does not have a matching record in the Patient table. The data is inconsistent, and the visit record is considered to be an orphaned record.
• In another situation, Donna changes the PatientID in the Patient table for Drew Wagner from 13256 to 13510. Because the Patient table no longer has a patient with the PatientID 13256, this change creates two orphaned records in the Visit table, and the database is inconsistent.
• In a third scenario, Donna deletes the record for Drew Wagner, Patient 13256, from the Patient table because this patient has moved and no longer receives care from Lakewood. The database is again inconsistent; two records for Patient 13256 in the Visit table have no matching record in the Patient table.

You can avoid these types of problems and avoid having inconsistent data in your database by specifying referential integrity between tables when you define their relationships.

AC 2-15bReferential Integrity

Referential integrity is a set of rules that Access enforces to maintain consistency between related tables when you update data in a database. Specifically, the referential integrity rules are as follows:

• When you add a record to a related table, a matching record must already exist in the primary table, thereby preventing the possibility of orphaned records.
• If you attempt to change the value of the primary key in the primary table, Access prevents this change if matching records exist in a related table. However, if you choose the Cascade Update Related Fields option, Access permits the change in value to the primary key and changes the appropriate foreign key values in the related table, thereby eliminating the possibility of inconsistent data.
• When you attempt to delete a record in the primary table, Access prevents the deletion if matching records exist in a related table. However, if you choose the Cascade Delete Related Records option, Access deletes the record in the primary table and also deletes all records in related tables that have matching foreign key values.

Insight

Understanding the Cascade Delete Related Records Option

Although using the Cascade Delete Related Records option has some advantages for enforcing referential integrity, it presents risks as well. You should rarely select the Cascade Delete Related Records option because doing so might cause you to inadvertently delete records you did not intend to delete. It is best to use other methods that give you more control over deleting records.

AC 2-15cDefining a Relationship between Two Tables

When two tables have a common field, you can define a relationship between them in the Relationships window, as shown in the Session 2.2 Visual Overview. Next, you’ll define a one-to-many relationship between the Patient and Visit tables, with Patient as the primary table and Visit as the related table, and with PatientID as the common field (the primary key in the Patient table and the foreign key in the Visit table). You’ll also define a one-to-many relationship between the Visit and Billing tables, with Visit being the primary table and Billing being the related table, and with VisitID as the common field (the primary key in the Visit table and a foreign key in the Billing table).

To define the one-to-many relationship between the Patient and Visit tables:

1. 1.

On the ribbon, click the Database Tools tab.

2. 2.

In the Relationships group, click the Relationships button to display the Relationships window and then click the Show Table button to open the Show Table dialog box. See Figure 2-39.

Figure 2-39Show Table dialog box

 

You must add each table participating in a relationship to the Relationships window. Because the Patient table is the primary table in the relationship, you’ll add it first.

3. 3.

 Click Patient, and then click the Add button. The Patient table’s field list is added to the Relationships window.

4. 4.

Click Visit, and then click the Add button. The Visit table’s field list is added to the Relationships window.

5. 5.

Click the Close button in the Show Table dialog box to close it.

So that you can view all the fields and complete field names, you’ll resize the Patient table field list.

6. 6.

Position the pointer on the bottom border of the Patient table field list until it changes to a two-headed arrow , and then drag the bottom of the Patient table field list to lengthen it until the vertical scroll bar disappears and all the fields are visible.

To form the relationship between the two tables, you drag the common PatientID field from the primary table to the related table. Access opens the Edit Relationships dialog box, in which you select the relationship options for the two tables.

7. 7.

Click PatientID in the Patient field list, and then drag it to PatientID in the Visit field list. When you release the mouse button, the Edit Relationships dialog box opens. See Figure 2-40.

Figure 2-40Edit Relationships dialog box

 

 

 

The primary table, related table, common field, and relationship type (One-To-Many) appear in the dialog box. Access correctly identifies the “One” side of the relationship and places the primary table Patient in the Table/Query section of the dialog box; similarly, Access correctly identifies the “Many” side of the relationship and places the related table Visit in the Related Table/Query section of the dialog box.

8. 8.

Click the Enforce Referential Integrity check box. The two cascade options become available. If you select the Cascade Update Related Fields option, Access will update the appropriate foreign key values in the related table when you change a primary key value in the primary table. You will not select the Cascade Delete Related Records option because doing so could cause you to delete records that you do not want to delete; this option is rarely selected.

9. 9.

Click the Cascade Update Related Fields check box.

10. 10.

Click the Create button to define the one-to-many relationship between the two tables and to close the dialog box. The completed relationship appears in the Relationships window, with the join line connecting the common field of PatientID in each table. See Figure 2-41.

Figure 2-41Defined relationship in the Relationships window

 

 

 

Trouble? If a dialog box opens indicating a problem that prevents you from creating the relationship, you most likely made a typing error when entering the two records in the Patient table. If so, click OK in the dialog box and then click Cancel in the Edit Relationships dialog box. Refer back to the earlier steps instructing you to enter the two records in the Patient table and carefully compare your entries with those shown in the text, especially the PatientID field values. Make any necessary corrections to the data in the Patient table, and then repeat Steps 7, 8, 9, and 10. If you still receive an error message, ask your instructor for assistance.

The next step is to define the one-to-many relationship between the Visit and Billing tables. In this relationship, Visit is the primary (“one”) table because there is at most one visit for each invoice. Billing is the related (“many”) table because zero, one, or many invoices are generated for each patient visit. For example, some visits require lab work or pharmacy charges, which is invoiced separately.

To define the relationship between the Visit and Billing tables:

1. 1.

On the Relationship Tools Design tab, in the Relationships group, click the Show Table button to open the Show Table dialog box.

2. 2.

 Click Billing on the Tables tab, click the Add button, and then click the Close button to close the Show Table dialog box. The Billing table’s field list appears in the Relationships window to the right of the Visit table’s field list.

3. 3.

Click and drag the VisitID field in the Visit field list to the VisitID field in the Billing field list. The Edit Relationships dialog box opens.

4. 4.

In the Edit Relationships dialog box, click the Enforce Referential Integrity check box, click the Cascade Update Related Fields check box, and then click the Create button to define the one-to-many relationship between the two tables and to close the dialog box. The completed relationships for the Lakewood database appear in the Relationships window. See Figure 2-42.

Figure 2-42Two relationships defined

 

 

 

5. 5.

On the Quick Access Toolbar, click the Save button  to save the layout in the Relationships window.

6. 6.

On the Relationship Tools Design tab, in the Relationships group, click the Close button to close the Relationships window.

7. 7.

  Compact and repair the Lakewood database, and then close the database.

Proskills