Specify the file txt, available for download from the SAM website, as the source of the data.

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_accdb
• Open the file you just downloaded and save it with the name:
  • NP_Access2013_T2_P1a_FirstLastNameaccdb
  • 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:
  • accdb
  • txt
  • 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

1. Open the Therapist table in Design View, and then set the field properties shown in Table 1 on the following page. Ensure the TherapistID field is 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

 

2. Add a new field to the table after the HireDate Use the field name Minors, the Yes/No data type, the Yes/No Format property, the default value No, and the caption Accepts Minors.
3. Delete the College field from the table. Save the Therapist (Hint: Click the Yes button when asked about potential data loss.)
4. In Datasheet View for the Therapist table, indicate that the following therapists will accept patients who are minors: Reese, McHarg, and Zachry.
5. While still in Datasheet View, resize the Accepts Minors column in the Therapist table to best fit the data it contains. Save and close the Therapist
6. Import the structure of the Patient table in the accdb database, available for download from the SAM website, into the current database. Do not save the import steps.
7. Open the Patient table in Design View, and then update the field properties using the information in Table 2 on the following page. Ensure the PatientID field is set as the primary key for the table. When you have finished, save the Patient

 

 

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

 

8. Add the records shown in Table 3 below to the Patient 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

 

9. Jennifer exported her existing patient data to a text file, and she asks you to add this data to the Patient Import the data as follows:
   1. Specify the file txt, available for download from the SAM website, as the source of the data.
   2. Select the option to append a copy of the records to the Patient table.
   3. In the Import Text Wizard dialog boxes, choose the options to import delimited data, to use a comma delimiter, and to import the data into the Patient table. Do not save the import steps.
10. Open the Billing table in Design View, and then set the field properties shown in Table 4 Ensure the BillingID field is 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

 

11. In Design View for the Billing table, move the Sessions field so it follows the EndDate Save and close the table.
12. Use the Import Spreadsheet Wizard to add data to the Billing table from an Excel spreadsheet as follows:
    1. Specify the file xlsx, available for download from the SAM website, as the source of the data.
    2. Select the option to append a copy of the records to the Billing
    3. In the Import Spreadsheet Wizard dialog boxes, choose the Sheet1 worksheet, and import to the Billing Do not save the import steps.
13. In Design View, create a new table using the information shown in Table 5 Ensure the LocationID field is 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

 

14. 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 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