The Alamogordo SPA VIP has a database with customer, employee, product, and service information for easier scheduling. An intern created the database, and the manager and staff members are struggling to use it to its fullest capacity. Recently, you have been hired to work in the office of the SPA manager, precisely because you have knowledge of Access. The manager asked you to start by maintaining the records and creating forms and reports to help better use the data in the database.
Add your information to the Staff table. You are Staff 15, living in an apartment rented by the company at 345 W Bend Avenue, in Santa Fe, NM, your phone is (505)-515- LastFourDigitis of Student number.
Change a client's record in the Client table: Client ID CL0-9, last name Bowers, has a new phone number (828)-555-4321
Alex Weaver, a Staff member also has a new telephone number: (505)-555-6666.
Remove a member's record from the table of staff members. Lucas Gill has left the company and Joshua Ripley has taken over his clients.
Using All Fields create a Columnar form layout named frmClientInput [See Figure 01] Add a label in the form's footer: Created by and type your first initial and last name
In the Design tab change the Theme to use the Access-theme
In the Themes group, change the Font to Garamond
Change the form title, to Client Input
Figure 01: frmClientInput: see image.
Please note: Several exercises specify the addition of this footer. Be sure that when they do the exercises, don't forget to place your name in the footers as directed.
Create another form that shows each client in the main form and the client's scheduled appointments in the subform [See Figure 02].
Your Form/Subform must include the company's logo under Email and above the subform.
[The logo is available as Access-image]
Figure 02: frmClientSchedule: see image.
Create a split form from the client table.
Figure 03: frmClientSplit: see image.
In the Appendix to this assignment, you will find examples of all printed reports you will generate.
In this exercise, you will use the form frmClientInput to print the record for Hamish Dunn.
Print it as a PDF file named: Dunn Info by LastName.pdf
SUBTASK A: Create and Print a report that will consist of a list of staff names and phone numbers so the staff can contact each other. The report must include the Staff Las Name, First Name and Phone Number, sorted by Last Name first and then by First Name. Save the report as rptStaffList and print it as PDF file named Staff Phone Directory by LastName.pdf
SUBTASK B: Create a multiple-table report that the staff can use to coordinate their services for a guest who may be seeing more than one staff member in a day. Use Table: tblStaff, StaffFirstName and StaffLastName and Table: tblSchedule. Procedure, DateOfAppt, and Client. Using normal grouping on the date of appointment sorted by client using Landscape orientation. Verify that all fields fit on a page. Save the report as rptStaffSchedule. Use this report to print a Staff Schedule to pdf named: Staff Schedule by LastName.pdf
SUBTASK C: Create a report to provide a daily total as well as a grand total. Using tblInvoice, InvoiceDate and InvoiceTotal use InvoiceDate, in the Grouping Options Grouping intervals using Day, sorted by InvoiceDate. In the Summary Options use Sum. Save it as rptInvoiceTotals and use it to print a pdf report named: Invoices Total by LastName.pdf
SUBTASK D: Create a report and apply conditional formatting to all services currently scheduled that cost more than $125. Using tblSchedule, DateOfAppt, Client, and Procedure and tblProcedure, Fee - Sorted by DateofAppt, save it as rptHighFees modify the title as High Procedure Clients. Using conditional formatting create a new rule. Verify that Check values in the current record or use an expression is highlighted. Find the three condition text boxes. The first should display Field Value Is. Click in the second condition box and select greater than. In the third condition text box, type 125. Below the condition text boxes, click Bold, click the Font color arrow, and then click Red in the last row. Click OK, verify that your rule states Value >125, and then click OK. Use it to print to pdf a report named: High Procedure Clients by LastName.pdf
Create a report using tblSchedule, DateOfAppt, Procedure, Client, and Staff. Grouping Options..., Grouping intervals Normal. Sorted by Procedure, and save it as rptAppointments [HINT: In Layout view, click the DateOfAppt text box control, and click the Format tab. In the Font group, click Align Text Left. Drag the right border of the DateOfAppt text box to line up with the left border of the Procedure text box so that all the date values are visible. Click the Procedure text box control, and then drag the left border to the left to make the control wider so all the text can be displayed. Change the title text to Daily Appointments Click the Design tab, and in the Grouping & Totals group, click Group & Sort. Click the line that displays Sort by Procedure and delete it. Click Add a group in the Group, Sort, and Total pane and select Staff. Click the Staff text box control, and then drag it to the left until it is under the date. Select the Staff and DateOfAppt label controls and delete them. Close the Group, Sort, and Total pane, close the report, and then click Yes.] Use the report to print to PDF and save it as Daily Appointments by LastName.pdf
Figure 04: rptAppointments: see image.