YO19_Access_Ch02_PS1 – Planner 1.0

Project Description:

Sue Morris has a small event-planning business. She recently decided to transfer her company’s data to a database. Although she has started on the database, she is too busy to finish it, so she has hired you to complete the implementation.

Steps to Perform:

Step

Instructions

Points Possible

Start Access. Download and open the file named Access_CH02_PS1_Planner.accdb. Grader has automatically added your last name to the beginning of the filename. Click Enable Editing, if necessary.

Open tblClient in Design view. Define an input mask for Phone. Use a mask that will show phone numbers as (555) 555-5555 with a placeholder of _ and save with the symbols in the mask.

Change the length of State to 2. Change the field size of ZipCode to 5. Save and close the table.

Import the text decorations data stored in Access_CH02_PS1_PlannerDec.txt into a new table. The data is delimited and separated with commas. Choose your own primary key of Field1. Save the table as tblDecoration. Do not save the import steps.

Open tblDecoration in Design view. Make the following changes to the fields:

Current Field Name	New Field Name	Data Type	Field Size
Field1	DecorationID	Number	Long Integer
Field2	Decoration	Short Text	20
Field3	Color	Short Text	10
Field4	Category	Short Text	20

Add the description A unique identifier for the decoration to the DecorationID field. Save and close the table.

Sue wants to be able to record the decorations that are being reserved for each event. This creates a many-to-many relationship between events and decorations. Create a junction table in Design view.

Create a new table in Design view. This will be a junction table to allow the many-to-many relationship between tblDecoration and tblEvent. Add the following fields using the corresponding data types (in this order):

Field Name	Data Type	Field Size
EventID	Number	Long Integer
DecorationID	Number	Long Integer
NumberReserved	Number	Long Integer

Add the description The number of decorations reserved to the NumberReserved field. Create a composite primary key using EventID and DecorationID. Save the new table as tblEventDecoration. Close the table.

Open the Relationships window. Create a one-to-many relationship between ClientID in tblClient and ClientID in tblEvent. Enforce referential integrity. Do not cascade update or cascade delete.

Add tblEventDecoration to the window. Create a one-to-many relationship between EventID in tblEvent and EventID in tblEventDecoration. Enforce referential integrity. Do not cascade update or cascade delete.

Create a one-to-many relationship between DecorationID in tblDecoration and DecorationID in tblEventDecoration. Enforce referential integrity. Do not cascade update or cascade delete. Save the changes.

Create a relationship report, naming the report Relationships for Access_CH02_PS1_Planner. Close the report and close the Relationships window.

Type the following data in the specified order into tblEventDecoration:

EventID	DecorationID	NumberReserved
3	7	4
3	9	40
3	17	4

Close the table.

Use the Report Wizard to create a report showing decorations needed for each event.

Use the LastName and FirstName fields from tblClient.
Use the EventDate field from tblEvent.
Use the Decoration and Color fields from tblDecoration.
Use NumberReserved from tblEventDecoration.
View by tblClient, and accept the default grouping.
Sort by Decoration in ascending order.
Change Orientation to Landscape.
Save the report as rptEventDecorations.

View the report in Layout view. Modify the report title to be Event Reservations by A Student. Save and then close the report.

Close all database objects. Close the database and then exit Access. Submit the database as directed.

Total Points

100

QUESTION 2

YO19_Access_Ch04_Prepare – Spa, Part B 1.0

Project Description:

The Turquoise Oasis Spa 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, so 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.

Steps to Perform:

Step	Instructions	Points Possible
1	Start Access. Download and open the file named Access_Ch04_Prepare_Spa_B.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.	0
2	A report can be created by using one table, multiple tables, or a query. A single-table report is a report created from one table. Any or all of the fields can be selected. The spa manager would like to have a report to help the staff with scheduling. Use the Report Wizard to create a report that contains the following fields: EmpFirstName, EmpLastName, and EmpPhone from the tblEmployee table. Do not create any groupings and sort the report in ascending order by EmpLastName and EmpFirstName. The report should have a Tabular layout and be in Portrait orientation. Allow the wizard to adjust the field width so all fields fit on a page. Name the report rptEmployeeList. In Design view, add a label to the top-left corner of the Report footer area about 2.5 inches wide. In the label, type Created by c Smith. Edit the report title to be Employee List. Save and close the report.	10
3	The manager would like a report that will show all employees names and their upcoming appointments. This way, the staff members can help coordinate their services for a guest who may be seeing more than one staff member in a day. Use the Report Wizard to create a report that contains the following fields: EmpFirstName and EmpLastName from tblEmployee Service, DateOfAppt, and Customer from tblSchedule. View the data by Employee. Create a grouping using DateOfAppt and modify the grouping intervals to be Normal. Sort the records in the report in ascending order by Customer. Organize the report with a Stepped layout and a Landscape orientation. Allow the wizard to adjust the field width so all fields fit on a page. Name the report rptEmployeeSchedule.	15
4	Layout view allows you to change basic design features of the report while the report is displaying data so the changes you make are immediately visible. You can resize controls, add conditional formatting, and change or add titles and other objects to the report in Layout view. Switch to Layout view of the rptEmployeeSchedule report. If the dates in the DateOfAppt field are not completely visible, adjust the width of the label and text box by dragging the left border to the left so that all dates are fully visible. In Design view, add a label to the top-left corner of the Report footer area about 2.5 inches wide. In the label, type Created by c Smith Save and close the report.	10
5	When you report on numeric data, Access has the ability to calculate sums, averages, minimums, and maximums of the numeric data. The grand total calculates the total for all records. Subtotals calculate totals for smaller groups of records. In this exercise, you will use the wizard to request these totals. Later, you will add totals to an already created report. The spa manager asks you to create a report showing the invoices collected each day. Use the Report Wizard to create a report that contains the following fields: InvoiceDate and InvoiceTotal from the tblInvoice table. Create a grouping using the InvoiceDate field and modify the grouping intervals to be by Day. Sort the records in the report in ascending order by InvoiceDate and use the Summary Options to sum the InvoiceTotal field. Organize the report with a Stepped layout and a Portrait orientation. Name the report rptInvoiceTotals. In Design view, add a label to the top-left corner of the Report footer area about 2.5 inches wide. In the label, type Created by c Smith. Save and close the report.	15
6	Controls, as defined in the section on forms, are also used in reports. A control can be a text box or another object that has been added to the form either by the wizard or manually in Layout or Design view. Controls can be moved or resized to make the report more readable. Open the rptEmployeeSchedule report in Layout view. Move the DateOfAppt text box control to the left so that it is slightly indented under the employee last name. Delete the First Name, Last Name, and DateOfAppt label controls. Move the Customer label, Customer text box, Service label, and Service text box so that they are next to the date field. Drag the right border of the Service text box to the right so that all service descriptions are visible. Change the Theme Colors to Green. Change the Shape Outline to Transparent for the Service and Customer text box controls. Apply a Bold font style and change the Shape Fill to Dark Teal Accent 4 Lighter 80% for the First Name and Last Name text box controls. Edit the report title to be Employee Schedule. Save and close the report.	12
7	Conditional formatting can be applied to any field and if the value in the meets the conditions you specify, the formatting will be applied. This is a useful tool to automatically highlight sales numbers on a report if they meet a certain threshold or to highlight students grades when they exceed a certain limit. Use the Report Wizard to create a report that contains the following fields: DateOfAppt, Customer, and Service from tblSchedule Fee from tblService. Have the wizard view the report by tblSchedue. Do not create any groupings. Sort the records in the report in ascending order by DateOfAppt. Organize the report with a Tabular layout and a Portrait orientation. Allow the wizard to adjust the field width so all fields fit on a page. Name the report rptHighFees. In Layout view, exit the report title to be High Service Customers. Apply Conditional Formatting to the Fee text box control so that if the value is greater than $100 the value appears Bold with a Dark Red font color. In Design view, add a label to the top-left corner of the Report footer area about 2.5 inches wide. In the label, type Created by c Smith. Save and close the report.	9
8	The Report Wizard gives you the opportunity to sort and group records. Sometimes reviewing the report reveals an alternative way to group and sort the data. You can change the sorting and grouping options from either Layout view or Design view. Groups are added to a section of the report called the group header. Calculations performed on a group in a report are added to a section called the group footer. A report may have one or more Group Headers, Group Footers, both, or neither. Use the Report Wizard to create a report that contains the following fields: DateOfAppt, Service, Customer, and Employee from tblSchedule. Remove the default grouping by the Service field and instead, create a grouping using the DateOfAppt field. Change the grouping intervals to Normal. Sort the records in the report in ascending order by Service. Organize the report with a Stepped layout and a Portrait orientation. Allow the wizard to adjust the field width so all fields fit on a page. Name the report rptAppointments. In Design view, add a label to the top-left corner of the Report footer area about 2.5 inches wide. In the label, type Created by c Smith. Switch to Layout view. Change the text alignment of the DateOfAppt text box to Align Text Left and drag the right border so that it lines up with the left order of the Service text box so that all date values are visible. Drag the left border of the Service text box to the left until all service descriptions are visible. Edit the report title to be Daily Appointments. Using the Group, Sort, and Total pane, delete the Sort by Service sorting sequence that was applied in the wizard. Add a new group for the Employee field. Move the Employee text box control to the left until it is under the date. Delete the Employee and DateOfAppt label controls. Save and close the report.	15
9	You can add totals in Layout view, using the Group, Sort, and Total pane when you are selecting or modifying groups and sorts for the reports. Open the rptDailyInvoices report and switch to Design view. In Design view, add a label to the top-left corner of the Report footer area about 2.5 inches wide. In the label, type Created by c Smith. Switch to Layout view and drag the left border of the Invoice Total label and text box controls to the right so that the fields is narrower but the column heading still shows. Edit the report so that it includes totals (Sum) for the Invoice Totals field. Set a caption for the subtotal control and then change the label text to Invoice Subtotal. Set a caption for the grand total control and then change the label text to Invoices Total. Save and close the report.	14
10	Close all database objects. Close the database and then exit Access. Submit the database as directed.	0

Total Points

100

^{"Place your order now for a similar assignment and have exceptional work written by our team of experts, guaranteeing you "A" results."}