GO19_AC_COMP_GRADER_CAP1 - Front Range 1.1

Step

Instructions

Points Possible

1

Start Access. Open the file Student_Access_aCCap_Front_Range.accdb downloaded with this project.

0

2

Open the CCap Inventory table in Design view. Add a new field between Retail Price and Category. The field should be named Cost and it should be calculated as [Retail Price]*.42. Format the field as Currency.

4

3

Add a new field at the bottom of the field list; name the field Item Image and select a Data Type that will allow an attachment. Switch to Datasheet view, saving the changes. Attach the downloaded file aCCap_Snowboard.jpg to the first record in the table. Close the table.

2

4

Create a new table in Design view using the following information for field design and properties. Determine the appropriate Data Type based on the field details.

Save the table as CCap PT Employees. Close the table.

9

5

Create a form in Form Design. For the Record Source, use the CCap PT Employees table. The form should include all fields in the table. Move Availability, Start Date, Hourly Wage, and Max Hours (in that order) to a second column with about 0.5 inches separating them. Align the Empl ID and Availability fields at the top. Reduce the height of the Detail area to 2.0 inches.

4

6

In the Form Header, add a logo using the downloaded file aCCap_Logo.jpg. Resize the logo so it is 1.5 inches wide and 0.5 inches tall. Add a title CCap Part Time Employees Form. Increase the font size of the title text to 20 points. Add a label at the left edge of the Form Footer that reads Form Designed by Quito Lester and format the label text as Bold.

4

7

Under Theme Colors, change the background color of the Form Header and Form Footer to the fourth color in the third row. Use the same color to outline the label controls in the Detail section of the form. Apply a line thickness of 1 pt. Save the form as CCap PT Employees Form.

2

8

In the Detail area, add an action button at the 2-inch mark on the horizontal ruler and 1.5-inch mark on the vertical ruler. The button should be used to print the current form using the default image to identify it. Name the button btnPrtFrm. Add a second action button about an inch to the right of the first. The second button should be used to close the form, using the default image to identify it. Name the second button btnClsFrm. Remove both buttons as tab stops on the form. Apply an outline that is the same color and thickness as the label controls in the form. Align the buttons their top borders.

4

9

Save the form. Switch to Form view. Add the following records using the form:

Empl Id First Name Last Name Availability Start Date Hourly Wage Max Hours

CB241 Chris Owen W 02/15/2018 10.25 10

CL965 Carol Labady D 08/27/2014 12.50 25

LM308 Lee Matthews A 05/20/2019 14.25 30

DT642 David Thomas E 03/23/2016 12.00 20

ED032 Elaine Carl W 10/20/2011 10.50 20

Close the form.

3

10

Design a query to display the number of items (using Item ID) by Category (add the two fields in that order). Change the Countof Item ID caption to Total. Run the query, and apply Best Fit to all columns. Save the query as CCap Inventory Query. Close the query.

4

11

Design a query to update the Retail Price for all Shoes to reflect a 21% discount (adding the Retail Price field first, and then the Category field). The new retail price will be 79% of the original price. Save the query as CCap Update Query. Run the query only once. Close the query.

4

12

Design a query to display each part-time employee’s First Name, Last Name, Hourly Wage, Max Hours, and Max Earnings, which will be calculated by multiplying the Hourly Wage by the Max Hours. Add the fields in this order. Run the query. Save the query as CCap Calculated Query. Close the query.

5

13

Design a query to display the Company, Category, and Retail Price (in that order) for all records. Run the query. Save the query as CCap Crosstab Setup Query. Close the query. Use the Query Wizard to create a crosstab query based on CCap Crosstab Setup Query. Display Company as the row heading, Category as the column heading, and an average of Retail Price. Do not display row sums. Save the query as CCap Crosstab Query. View the query results. AutoFit all columns. Close the query, saving changes.

5

14

Select the CCap Crosstab Query, and using the Report Wizard, create a crosstab report. Display all fields in the report. There will be no grouping. Sort the report by Company, in ascending order. Use a tabular format and Landscape orientation. Delete the Page Number control. Title the report CCap Average Retail Prices by Company and Category. Close the report.

4

15

Create a report in Design view based on the CCap Inventory table. Display the following fields in the report: Item Name, Retail Price, Category, and Sport (in that order). Place them at the 1.5-inch mark on the horizontal ruler and one dot below the Detail section bar. Save the report as CCap Inventory by Sport Report.

4

16

Group the report by Sport with a footer section. Keep the whole group together on one page. Move the Sport controls to the Sport Header section. Delete the Sport label control, and then move the Sport textbox control to the left edge of the Sport Header section. Sort the report by Item Name, in ascending order. Resize any text controls so all data is visible.

3

17

Reduce the height of the Detail section to 1 inch. In the Report Header, add a logo using the downloaded file aCCap_Logo.jpg. Resize it to 1.5 inches wide and 0.5 inches high.

2

18

In the Report Header, add the title CCap Inventory by Sport. Bold and Center the title. Resize the title control so that the right edge aligns with the 7-inch mark on the horizontal ruler.

1

19

In the Sport Footer, add a calculation that will count the number of items per sport. Add a label containing the text Total Number of Items to the left of the total control. In the Report Footer, add a calculation that will count the number of items per sport for the report. Add a label containing the text Total Inventory Items to the left of the total control.

3

20

Add a line at the bottom of the Sport Footer that extends from the 0-inch mark to the 6.75-inch mark on the horizontal ruler. Under Theme Colors, change the outline color to the sixth color in the fifth row and line thickness to 2pt. Change the font color of the label control and text box control in the Sport Footer to match the line. Change the border style of the outline around the Sport Footer to transparent.

4

21

Add Page N of M numbering in the bottom center of the page. Resize the width of the report to 8 inches. In the Report Footer, add a label control beginning at the 4.25 inch mark on the horizontal ruler and aligned at the right edge of the report. It should read Form Designed by Quito Lester. Save the report. Close the report.

2

22

Create a macro with a comment: Purpose: To open the CCap Applicants Form for editing and then, in the Show/Hide group, be sure the Show All Actions button is active. Save the macro as OpenAppForm.

2

23

In the Macro Designer, add the action DisplayHourglassPointer with the Comment Displays the busy icon. Add the Echo action, change the Echo On box to No, and add the Status Bar Text Macro is executing.

2

24

In the Macro Designer, add the action OpenForm to open the CCap Applicants Form in Form view and Edit Mode. Add a comment that reads Displays the CCap Applicants Form.

2

25

In the Macro Designer, add an Echo action. In the Echo Action Block, be sure the Echo On box is set to Yes. Add a DisplayHourglassPointer action. In the DisplayHourglassPointer Action Block, change the Hourglass On box to No. Add a Comment that reads Displays mouse pointer.

2

26

In the Macro Designer, add a MessageBox action. Type a message that displays Confidential! For use by the Human Resources Department ONLY. Add a Beep, and a Warning! Icon. Title the Message box Confidential Information. Add a Comment that reads Displays a security message and then close the Macro Designer, saving changes.

2

27

Create a table in the database by importing the downloaded Excel worksheet workbook named aCCap_Hours_Scheduled.xlsx. Indicate that the first row contains column headings and choose Empl ID as the primary key. Name the table CCap 1st Q Hours. Do not save the import steps.

3

28

Merge the CCap Suppliers table with the aCCap_Advertising_Letter.docx file. Maximize the Word window. Delete the words Inside Address, and insert the Address Block field in its place. Insert the Company field after the space following Attention. Save the main document as CCap_Ltr_Main. Use the Snipping Tool to create a screenshot of your screen, and save the file as a JPEG using the file name LtrMain.jpg. In the database, create a Blank Form, and then insert the image file, LtrMain.jpg in the upper left corner. Save the form as CCap Merge. Close the form.

5

29

Open the CCap Pricing Report in Design view. Increase the height of the Report Footer section to the 3-inch mark on the vertical ruler. Open the downloaded Excel workbook aCCap_Averages.xlsx. Copy and Paste the chart into the Report Footer section in the report. Save and close the report.

2

30

Create a Navigation form using the Vertical Tabs Left layout. Close the Field List. Add the CCap Average Retail Prices by Company and Category report, CCap Inventory by Sport Report, and CCap Pricing Report to the Navigation Form. Save the form as CCap Navigation Form. Close the form.

5

31

From Backstage view, display the Access Options dialog box. For the Current Database, set the Application Title as Front Range Action Sports. Set the Display Form as CCap Navigation Form.

2

32

Customize the Quick Access toolbar for the current database only by adding the New and Quick Print icons. Click OK to close the dialog box. Click OK to close the message box.

0

33

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

0

Total Points

100