NP_Access2013_T2_P1b_FirstnameLastname_2

New Perspectives Access 2013

Tutorial 2: SAM Project 1b

PRN NURSING SERVICES

UPDATING TABLES, ADDING RECORDS, AND CREATING RELATionships

PROJECT DESCRIPTION

Amy Rosario uses a database to store and maintain data about the contractors, clients, and billing records for PRN Nursing Services. She asks you to help her continue building the database by changing the properties for fields in three tables, creating a fourth table (in which she will eventually store data about offices), adding records, and creating the table relationships.

GETTING STARTED

· Download the following file from the SAM website:

o NP_Access2013_T2_P1b_FirstLastName_1.accdb

· Open the file you just downloaded and save it with the name:

o NP_Access2013_T2_P1b_FirstLastName_2.accdb

o 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:

o support_NP_A13_T2_P1b_prn.accdb

o support_NP_A13_T2_P1b_client.txt

o support_NP_A13_T2_P1b_invoices.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 Contractor table in Design view, and then set the field properties shown in Table 1 on the following page. The ContractorID field should be set as the primary key for the table.

Table 1: Contractor Table – Field Properties

Field Name	Data Type	Description	Field Size	Other
ContractorID	_{Short Text}	_{Primary key}	₄	_{Caption: Contractor ID}
LastName	Short Text		25	Caption: Last Name
FirstName	Short Text		20	Caption: First Name
Interests	Short Text		50
Credentials	Short Text		20
College	Short Text		50
HireDate	Date/Time			Format: Short Date Caption: Hire Date

2. Add a new field to the table, so it appears after the HireDate field. Use the field name PA, the Yes/No data type, the Yes/No Format property, the Default Value property No, and the Caption property Physician Asst (do not include a period at the end of the caption).

3. Delete the College field from the table. Save the Contractor table. (Hint: Click the Yes button when asked about potential data loss.)

4. In Datasheet view for the Contractor table, indicate that the following contractors are physician assistants: Johnson, Neumans, Mazuelos, McCabe, and Lacey.

5. Resize the Physician Asst column in the Contractor datasheet to best fit the data it contains. Save and close the Contractor table.

6. Import the structure of the Client table in the support_NP_A13_T2_P1b_prn.accdb database, available for download from the SAM website, into the current database. Do not save the import steps.

7. Open the Client table in Design view, and then update the field properties using the information shown in Table 2 on the following page. The ClientID field should be set as the primary key for the table. When you have finished, save the Client table.

Table 2: Client Table – Field Properties

Field Name	Data Type	Description	Field Size	Other
ClientID	_{Short Text}	_{Primary key}	₆	_{Caption: Client 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: TN
Zip	Short Text		10
Phone	Short Text		15
ClientSince	Date/Time			Format: Short Date Caption: Client Since
Corp	Short Text	Y (Yes), N (No)	1

8. Add the records shown in Table 3 below to the Client table. Close the table when you are finished.

Table 3: Client Table - Records

Client ID	First Name	Last Name	Address	City	State	Zip	Phone	Client Since	Corp
A534	_Cliff	_Ash	_{29 Edgewater}	_Nashville	_TN	₃₇₂₀₁	_615-555-4863	_{1/6/ 2010}	_N
B506	Scott	McAdams	1815 Resaca	Murfreesboro	TN	37127	615-555-9000	5/15/2013	Y

9. Amy exported her existing client data to a text file and asks you to add this data to the Client table. Import the data as follows:

a. Specify support_NP_A13_T2_P1b_client.txt as the source of the data. (The file support_NP_A13_T2_P1b_client.txt is available for download from the SAM website.)

b. Select the option to append a copy of the records to the Client table.

c. 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 Client 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 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}	₄	_{Caption: Billing ID}
ClientID	Short Text	Foreign key	4	Caption: Client ID
ContractorID	Short Text	Foreign key	4	Caption: Contractor ID
StartDate	Date/Time			Format: Short Date Caption: Start Date
EndDate	Date/Time			Format: Short Date Caption: End Date
HourlyRate	Currency			Caption: Hourly Rate Decimal Places: 2
TotalHours	Number		Integer	Caption: Total Hours Decimal Places: 2

11. In Design view for the Billing table, move the TotalHours field so it follows the EndDate field. Save and close the table.

12. Use the Import Spreadsheet Wizard to add data to the Billing table from an Excel spreadsheet as follows:

a. Specify support_NP_A13_T2_P1b_invoices.xlsx as the source of the data. (The file support_NP_A13_T2_P1b_invoices.xlsx is available for download from the SAM website.)

b. Select the option to append a copy of the records to the Billing table.

c. In the Import Spreadsheet Wizard dialog boxes, choose the Sheet1 worksheet, and import to the Billing table. Do not save the import steps.

13. Amy wants to create a table in which she will eventually store data about PRN Nursing Services locations. In Design view, create a new table using the information shown in Table 5 below. The OfficeID field should be set as the primary key for the table. Save the table as Office, and then close the table.

Table 5: Office Table – Field Properties

Field Name	Data Type	Description	Field Size	Other
OfficeID	_{Short Text}	_{Primary key}	₃	_{Caption: Office ID}
Address	Short Text		35
City	Short Text		25
State	Short Text		2	Default Value: TN
Zip	Short Text		10
Phone	Short Text		15

14. Add the Contractor, Billing, and Client tables to the Relationships window. (Note: The Office table will be added to the Relationships window in another project.) 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 Contractor table and the related Billing table using the ContractorID field. Choose the options to enforce referential integrity and to cascade updates to related fields.

15. Define a one-to-many relationship between the primary Client table and the related Billing table using the ClientID 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

Microsoft product screenshots used with permission from Microsoft Corporation.

Save and close any open objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.

NOTE:

1. Move _GradingInfoTable table from your database to this database as it contains your name.

2. Rename database as per your FirstnameLastname.

3. Open each table in design view and change Data Type from "Text" to "Short Text". All Text data type should be short text. In my Access short text data type is not there.

All other things are taken care of.

Assignments

SAM Access 2013 Project-2: PRN NURSING SERVICES