BUSINESS 2400 D|E|F
Foundations of Information Systems Winter 2019
Access Assignment
Due: March 22, 2019 – 10:00 pm
Files you need: [Available in CU Learn]
AccessBUSI2400W19 Data | AccessBUSI2400W19 image | AccessBUSI2400W19 theme
Alamogordo VIP’s SPA
New Database
The Alamogordo SPA VIP has a database with customer, employee, product, and service information for easier scheduling and access. An
intern created the database, and the manager and staff members are struggling to use it to its fullest capacity. You have recently been hired to work in the office of the spa, and you have knowledge of Access and the manager has asked for your help in maintaining the records and creating forms and reports to help better use the data in the database.
Open the AccessBUSI2400W19 Data
Open the database and save it with a new file name. Save the file as:
AlamogordoSolutionByLastName.accdb
Task 1 – Edit Records in Tables
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. Task 2 – Delete a Record
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
Task 3 – Create a Single-Table Form
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 AccessBUSI2400W19 theme
In the Themes group, change the Font to Garamond
Change the form title, to Client Input
1 |P a g e
Figure 01: frmClientInput
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.
Task 4 – Create a Form with the Company’s Logo
Figure 02: frmClientSchedule
2 |P a g e
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 AccessBUSI2400W19 image]
Task 5 – Create a Split Form
Figure 03: frmClientSplit Create a split form from the client table.
Task 6 – Print a Record from a Form
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
Task 7 – Create and Print Reports
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. [HINT: In the Grouping & Totals group, click Totals, and then click Sum. Right-click one of the subtotal controls and click Set Caption. Double-click the label control containing “InvoiceTotal Total”, select the text, type Invoice Subtotal and press Enter. Repeat the same steps to set a caption for the grand total control and change the text to Invoice Total. Change the title to Invoice Amounts.] Save it as rptInvoiceTotals and use it to print a pdf report named: Invoices Total by LastName.pdf
3 |P a g e
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
Task 8 – Add Group and Sort Fields to a New Report
Figure 04: rptAppointments
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
Task 9 – Zip all your files to AccessSolutionByLastName.zip
The files you need to submit are:
1. AlamogordoSolutionByLastName.accdb
2. Dunn Info by LastName.pdf
3. Staff Phone Directory by LastName.pdf
4 |P a g e
4. Staff Schedule by LastName.pdf
5. Invoices Total by LastName.pdf
6. High Procedure Clients by LastName.pdf
7. Daily Appointments by LastName.pdf
Task 10 – Submit in CU Learn by the deadline
5 |P a g e
APPENDIX
6 |P a g e
Client ID First Name Last Name Address City
State Phone Email
Created by S. Farivar
Client Input
CL0-4
Hamish
Dunn
Ap #206-7268 Amet Street
West Warwick
MI
(734) 555-2525
Sed@nibhsitamet.ca
Staff Phone Directory
Last Name
Evans
Farivar
Fisher
Heath
Johnson Klein McIntosh Murphy Paul Ripley Rodriguez Weaver Weaver Welch
Created by S. Farivar
First Name
Lauren
Samira
Sarah
Elizabeth
Amanda
Peter
Amanda
Mary
Mariah
Joshua Brenda Alex Joseph Jessica
Phone
(505) 555-3800
(505) 555-1234
(505) 555-2525
(505) 555-4103
(505) 555-7943
(505) 555-0247
(505) 555-3242
(505) 555-6201
(505) 555-8217
(505) 555-6243
(505) 555-9244
(505) 555-6666
(505) 555-4149
(505) 555-4101
December 10, 2018
Page 1 of 1
Staff Schedule
Client
Procedure
Joseph
Weaver
January 14, 2018
January 17, 2018
January 19, 2018
January 13, 2018
January 14, 2018 January 17, 2018
January 18, 2018 January 19, 2018
January 14, 2018
January 19, 2018
January 13, 2018
January 16, 2018
Janet Omar Omar
Janet
Janet
Blaze Harper
Harper
Marvin
James
James
Kibo
Lavinia James
Janet
Swedish Massage
Swedish Massage
Back Treatment and Massage
Gentlemen’s Manicure
Gentlemen’s Pedicure
Back Treatment and Massage Parafin Pedicure
Parafin Manicure
Gentlemen’s Manicure
Gentlemen’s Pedicure
Gentlemen’s Manicure
Cut and Perm
Chakra Body and Balancing Massage Cut and Style
Gentlemen’s Facial
Troy Swedish Massage
Lauren
Evans
Sarah
Fisher
Elizabeth
Heath
December 10, 2018
Page 1 of 3
January 18, 2018
January 15, 2018
January 18, 2018
January 19, 2018
January 13, 2018
January 15, 2018
January 14, 2018
January 19, 2018
January 14, 2018
January 16, 2018
January 17, 2018
Client
Janet
Eden
Eden
Eden
Otto
Marvin
Nero
James
Samson Emily
Genevieve
Lavinia
Lavinia Brenden
Sara Haviva
Haviva
Procedure
Gentlemen’s Facial
Acne Relief Treatment
Customized Facial
Lip Wax
Cut and Style
Gentlemen’s Facial
Gentlemen’s Facial
Gentlemen’s Facial
Gentlemen’s Facial Eyebrow Waxing
Lip Wax
Eyebrow Waxing
Eyelash and Eyebrow Tinting Cut and Style
Cut and Style
Back Treatment and Massage
Clay Wrap
Amanda
Peter
Klein
Amanda
McIntosh
Mary
Murphy
December 10, 2018
Page 2 of 3
Johnson
Eden Eyebrow Waxing
Harper
Sea Salt Scrub
Joshua
Created by S. Farivar
December 10, 2018
Page 3 of 3
Ripley
January 13, 2018
January 14, 2018
January 19, 2018
January 14, 2018
January 16, 2018
January 19, 2018 January 14, 2018
January 15, 2018
January 19, 2018
Erica
Lois
Lavinia
Sara
Sara
Samson
Emily Haviva Sara
Mercedes Lavinia
Lavinia
Kibo
Kibo
Stress Relieving Massage
Back Treatment and Massage
Eye Zone Treatment
Stress Relieving Massage
Aroma Body Wrap
Seaweed Body Wrap
Cut and Color Customized Facial Sea Salt Scrub
Cut and Color Cut and Color
Strength Infusion
Eyebrow Waxing
Eyelash and Eyebrow Tinting
Client
Procedure
January 18, 2018
Eden Seaweed Body Wrap
Alex
Weaver
Jessica
Welch
Invoice Amounts InvoiceDate
Last Name
First Name InvoiceNumber
InvoiceTotal
Created by S. Farivar
December 10, 2018
Page 1 of 1
2018-01-15
2018-01-17
2018-01-18
Hagood
Janet TOS1101
$44.81 $44.81
Burton Kinney
Genevieve TOS1103 Lois TOS1102
$92.90
Silva Hogan Hinton Malone Joseph Berg Justino
Eden TOS11010
$25.25 $43.90 $15.95 $28.45 $51.40 $93.20 $18.50
Haviva Omar Lavinia Mercedes Emily Sara
TOS1109 TOS1108 TOS1107 TOS1106 TOS1105 TOS1104
Invoice Subtotal
Invoice Subtotal
$10.50 $103.40
Invoice Subtotal Invoice Total
$276.65 $424.86
High Procedure Clients
Date Client
January 13, 2018 January 13, 2018 January 13, 2018 January 14, 2018 January 14, 2018 January 14, 2018 January 14, 2018 January 15, 2018 January 15, 2018 January 16, 2018 January 16, 2018 January 17, 2018 January 17, 2018 January 18, 2018 January 18, 2018 January 18, 2018 January 18, 2018 January 19, 2018
Procedure
Fee
Janet Gentlemen’s Facial $130.00
January 13, 2018
Janet Gentlemen’s Manicure $30.00
January 13, 2018
Janet Gentlemen’s Pedicure $50.00
Genevieve Lip Wax $16.00
Lois Back Treatment and Massage $129.00
January 13, 2018
Emily Eyebrow Waxing $30.00
Lavinia Eyelash and Eyebrow Tinting $75.00
January 14, 2018
Janet Swedish Massage $145.00
Mercedes Cut and Color $162.00
January 14, 2018
Lavinia Chakra Body and Balancing Ma $119.00
Lavinia Eye Zone Treatment $50.00
January 14, 2018
Emily Cut and Color $162.00
Blaze Back Treatment and Massage $129.00
January 14, 2018
Sara Cut and Style $75.00
Lavinia Eyebrow Waxing $30.00
January 15, 2018
Lavinia Strength Infusion $45.00
Lavinia Cut and Color $162.00
January 15, 2018
Otto Cut and Style $75.00
Janet Gentlemen’s Facial $130.00
January 16, 2018
Haviva Customized Facial $78.00
Haviva Back Treatment and Massage $129.00
January 16, 2018
Haviva Clay Wrap $110.00
Harper Parafin Pedicure $60.00
January 17, 2018
Harper Parafin Manicure $35.00
Omar Swedish Massage $145.00
January 17, 2018
Harper Sea Salt Scrub $68.00
Marvin Gentlemen’s Facial $130.00
January 18, 2018
Marvin Gentlemen’s Manicure $30.00
Erica Stress Relieving Massage $55.00
January 18, 2018
Eden Lip Wax $16.00
Eden Seaweed Body Wrap $110.00
January 18, 2018
Eden Eyebrow Waxing $30.00
Eden Customized Facial $78.00
January 18, 2018
Eden Acne Relief Treatment $82.00
Kibo Eyebrow Waxing $30.00
January 19, 2018
Troy Swedish Massage $65.00
December 10, 2018
Page 1 of 2
Date Client
January 19, 2018 January 19, 2018 January 19, 2018 January 19, 2018 January 19, 2018 January 19, 2018 January 19, 2018
Created by S. Farivar
Procedure
Fee
January 19, 2018
Sara
Stress Relieving Massage
$55.00
James
Cut and Style
$75.00
James
Gentlemen’s Pedicure
$50.00
January 19, 2018
James
Gentlemen’s Manicure
$30.00
James
Gentlemen’s Facial
$130.00
January 19, 2018
Sara
Aroma Body Wrap
$99.00
January 19, 2018
Omar
Back Treatment and Massage
$129.00
Samson
Seaweed Body Wrap
$110.00
Samson
Gentlemen’s Facial
$130.00
January 19, 2018
Kibo
Cut and Perm
$157.00
Sara
Sea Salt Scrub
$68.00
January 19, 2018
Brenden
Cut and Style
$75.00
Nero
Gentlemen’s Facial
$130.00
January 19, 2018
Kibo
Eyelash and Eyebrow Tinting
$75.00
December 10, 2018
Page 2 of 2
Daily Appointments
January 13, 2018
Procedure
Client
Lauren
Gentlemen’s Manicure
Lip Wax
Janet
Genevieve
Gentlemen’s Pedicure
Janet
Elizabeth
Gentlemen’s Facial
Janet
Peter
Eyebrow Waxing
Emily
Joshua
January 14, 2018
Back Treatment and Massage
Lois
Joseph
Swedish Massage
Janet
Lauren
Back Treatment and Massage
Blaze
Sarah
Chakra Body and Balancing Massage
Lavinia
Amanda
Eyelash and Eyebrow Tinting
Lavinia
Mary
Cut and Style
Sara
Joshua
Eye Zone Treatment
Lavinia
Alex
Cut and Color
Emily
Jessica
Cut and Color
Mercedes
January 15, 2018
Amanda
Cut and Style
Strength Infusion
Gentlemen’s Facial
Otto
Lavinia
Janet
Peter
Eyebrow Waxing
Lavinia
Jessica
January 16, 2018
Cut and Color
Lavinia
Elizabeth
Mary
December 10, 2018
Page 1 of 3
Procedure
Back Treatment and Massage
Client
Haviva
January 17, 2018
Clay Wrap
Haviva
Alex
Customized Facial
Haviva
Joseph
Lauren
Swedish Massage
Omar
Parafin Manicure
Harper
Parafin Pedicure
Harper
Mary
Sea Salt Scrub
Harper
January 18, 2018
Lauren
Gentlemen’s Manicure
Marvin
Elizabeth
Acne Relief Treatment
Eden
Customized Facial
Eden
Eyebrow Waxing
Eden
Lip Wax
Eden
Amanda
Gentlemen’s Facial
Marvin
Mary
Seaweed Body Wrap
Eden
Stress Relieving Massage
Erica
January 19, 2018
Joseph
Lauren
Swedish Massage
Troy
Back Treatment and Massage
Omar
Gentlemen’s Manicure
James
Gentlemen’s Pedicure
James
Cut and Perm
Kibo
Sarah
Cut and Style
Cut and Style
James
Brenden
Amanda
Gentlemen’s Facial
Samson
Gentlemen’s Facial
James
Gentlemen’s Facial
Nero
Amanda
Joshua
December 10, 2018
Page 2 of 3
Procedure
Seaweed Body Wrap
Sea Salt Scrub
Client
Samson
Sara
Stress Relieving Massage
Sara
Aroma Body Wrap
Sara
Alex
Jessica
Eyebrow Waxing
Kibo
Eyelash and Eyebrow Tinting
Kibo
Created by S. Farivar
December 10, 2018
Page 3 of 3