Homework answers / question archive / Sunshine Mental Health Services provides counselling and medication services

Sunshine Mental Health Services provides counselling and medication services

MS Access

Share With

---

Sunshine Mental Health Services provides counselling and medication services. They have recently expanded their database to include patients in addition to the staff. You were hired to replace their former Information Technology support staff member. You will work to update the data in the database, familiarize yourself with the table relationships, filter and sort a table, and add a table to keep track of user accounts.

a. Open the a01m1Sunsitine file and save the database as a01 m1Sunshine_LastFirst. Click Enable Content on the Security Warning message bar.

b. Open the Staff table in Datasheet view.

c. Locate the record for Kovit Ang (StaffiD 80073). Replace his Address with 11 Market Street, replace his City with Harrison, and his ZIP Code with 04040. Leave all other fields with their current values.

d. Add yourself as a new staff member. Type a Staff ID of 99999 and type your name in the. Full Name field. Type 1 Clinton Terrace for your Address. Harrison as your City, ME as your State, and 04040 as your ZIP. Type a Job Code of 300, a Salary of 485000, and a 401k contribution of 0.02. Click the box in the Active field so a check box appears in the box.

e. Delete record 80399 (Stan Marsh).

f. Sort the table by Salary in descending order. Save and close the table.

g. Click Relationships in the Relationships group on the Database Tools tab and notice the relationship between the Position table and the Staff table, and the relationship between the Staff table and Patients table. Each position has staff associated with it, and staff members have patients associated with them. Close the Relationships window.

h. Rename the Pos table to Position. Add a description to the table stating This table contains a list of all available job titles at the company. Click OK.

i. Open the Position table in Datasheet view. Click the plus sign next to Job Code 100 (Social Worker). Notice seven social workers are employed by the company. Click the plus sign next to JobCode 300 (IT Support). Only your name should appear. Close the table.

j. Open the Patients table in Datasheet view. Use a Selection filter to show all patients associated with StaffID 80073. Save and close the table.

k. Open the Staff table in Datasheet view. Use Filter By Form to display all staff members who earn a salary of more than 80000. Toggle the filter to verify the results. Save and close the table.

I. Back up the database. Accept the default file name.

m. Add a Users application part to the database. Change the relationship so there is One 'Staff' to many 'Users' by clicking the arrow next to Patients and selecting Staff. Click Next. Select the FullName field from ‘staff’, choose the Sort Ascending option, and name the lookup column User. Click Create.

n. Open the Users table in Datasheet view.

o. Select Adolfo Ortiz in the User field. Type aortiz@sunshinementalhealth.org for Email and aortiz for Login. Leave the FullName blank. Close the table.   

p. Create a form based on the Patient table using the Form button in the Forms group of the Create tab. Save the form as Patient Data Entry.

q. Switch to Form view of the form. Delete the phone number for PatientID 1 (Minoru Kobayashi). Close the form.

r. Close the database and exit Access. Based on your instructor's directions, submit the following: a01m1Sunshine_LastFirst

a01m1Sunshine_LastFirst_CurrentDate