PROJECT DESCRIPTION
Jennifer Christie uses a database to store and maintain data about the therapists, patients, billing, and locations for Physical Therapy Specialists. She asks you to help her continue building the database by changing the properties for fields in three tables, creating a fourth table, adding records, and creating the table relationships.
GETTING STARTED
Download the following file from the SAM website:
NP_Access2013_T2_P1a_FirstLastName_1.accdb
Open the file you just downloaded and save it with the name:
NP_Access2013_T2_P1a_FirstLastName_2.accdb
Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically.
To complete this Project, you will also need to download and save the following support files from the SAM website:
support_NP_A13_T2_P1a_pts.accdb
support_NP_A13_T2_P1a_patient.txt
support_NP_A13_T2_P1a_billing.xlsx
Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
Open the Therapist table in Design view, and then set the field properties shown in Table 1 on the following page. The TherapistID field should be set as the primary key for the table.
Table 1: Therapist Table – Field Properties
Field Name | Data Type | Description | Field Size | Other |
TherapistID | Short Text | Primary key | 3 | Caption: Therapist ID |
LastName | Short Text | 25 | Caption: Last Name | |
FirstName | Short Text | 20 | Caption: First Name | |
Specialty | Short Text | 25 | ||
Certification | Short Text | 20 | ||
College | Short Text | 255 | ||
HireDate | Date/Time | Format: Short Date Caption: Hire Date |
Add a new field to the table, so it appears after the HireDate field. Use the field name Minors, the Yes/No data type, the Yes/No Format property, the default value No, and the Caption Accepts Minors.
Delete the College field from the table. Save the Therapist table. (Click the Yes button when asked about potential data loss.)
In Datasheet view for the Therapist table, indicate that the following therapists will accept patients who are minors: Reese, McHarg, and Zachry.
Resize the Accepts Minors column in the Therapist datasheet to best fit the data it contains. Save and close the Therapist table.
Import the structure of the Patient table in the support_NP_A13_T2_P1a_pts.accdb database, available for download from the SAM website, into the current database. Do not save the import steps.
Open the Patient table in Design view, and then update the field properties in using the information in Table 2 on the following page. The PatientID field should be set as the primary key for the table. When you have finished, save the Patient table.
Table 2: Patient Table – Field Properties
Field Name | Data Type | Description | Field Size | Other |
PatientID | Short Text | Primary key | 6 | Caption: Patient ID |
FirstName | Short Text | 20 | Caption: First Name | |
LastName | Short Text | 25 | Caption: Last Name | |
Address | Short Text | 35 | ||
City | Short Text | 25 | ||
State | Short Text | 2 | Default Value: OH | |
Zip | Short Text | 10 | ||
Phone | Short Text | 15 | ||
BirthDate | Date/Time | Format: Short Date Caption: Birth Date | ||
Gender | Short Text | F (Female), M (Male) | 1 |
Add the records shown in Table 3 below to the Patient table. Close the table when you are finished.
Table 3: Patient Table - Records
Patient ID | FirstName | LastName | Address | City | State | Zip | Phone | Birth Date | Gender |
A10026 | Al | Pandola | 45 South Main St | Akron | OH | 44307 | 330-555-4815 | 12/15/1961 | M |
B10585 | Maria | Green | 12 Malabar | Akron | OH | 44306 | 330-555-9855 | 2/28/1965 | F |
Jennifer exported her existing patient data to a text file and asks you to add this data to the Patient table. Import the data as follows:
Specify support_NP_A13_T2_P1a_patient.txt as the source of the data. (The file support_NP_A13_T2_P1a_patient.txt is available for download from the SAM website.)
Select the option to append a copy of the records to the Patient table.
In the Import Text Wizard dialog boxes, choose the option to import delimited data, to use a comma delimiter, and to import the data into the Patient table. Do not save the import steps.
Open the Billing table in Design view, and then set the field properties shown in Table 4 below. The BillingID field should be set as the primary key for the table.
Table 4: Billing Table – Field Properties
Field Name | Data Type | Description | Field Size | Other |
BillingID | Short Text | Primary key | 6 | Caption: Billing ID |
PatientID | Short Text | Foreign key | 6 | Caption: Patient ID |
TherapistID | Short Text | Foreign key | 3 | Caption: Therapist ID |
StartDate | Date/Time | Format: Short Date Caption: Start Date | ||
EndDate | Date/Time | Format: Short Date Caption: End Date | ||
Amount | Currency | Total contract amount | Decimal Places: 0 | |
Sessions | Number | Number of sessions | Integer |
In Design view for the Billing table, move the Sessions field so it follows the EndDate field. Save and close the table.
Use the Import Spreadsheet Wizard to add data to the Billing table from an Excel spreadsheet as follows:
Specify support_NP_A13_T2_P1a_billing.xlsx as the source of the data. (The file support_NP_A13_T2_P1a_billing.xlsx is available for download from the SAM website.)
Select the option to append a copy of the records to the Billing table.
In the Import Spreadsheet Wizard dialog boxes, choose the Sheet1 worksheet, and import to the Billing table. Do not save the import steps.
In Design view, create a new table using the information shown below in Table 5. The LocationID field should be set as the primary key for the table. Save the table as Location, and then close the table.
Table 5: Location Table – Field Properties
Field Name | Data Type | Description | Field Size | Other |
LocationID | Short Text | Primary key | 3 | Caption: Location ID |
Address | Short Text | 35 | ||
City | Short Text | 25 | ||
State | Short Text | 2 | ||
Zip | Short Text | 10 |
| |
Phone | Short Text | 15 |
Add the Therapist, Billing, and Patient tables to the Relationships window. Resize the field list for each table shown in the Relationships window so all fields are visible in the field list. Define a one-to-many relationship between the primary Therapist table and the related Billing table using the TherapistID field. Choose the options to enforce referential integrity and to cascade updates to related fields.
Define a one-to-many relationship between the primary Patient table and the related Billing table using the PatientID field. Choose the options to enforce referential integrity and to cascade updates to related fields. Figure 1 on the following page shows the completed Relationships window. Save and close the Relationships window.
Figure 1: Relationships Window
