In this project, you will apply skills you practiced from the objectives in Access Chapters 4-10. You will update the current database for Frank Osei, the Vice President of Finance, as he manages the company’s growth. You will modify existing tables and create a new one to track part-time employee information. In addition, you will create queries to assist Frank as he monitors the company’s performance and requests that data be updated. You will also create forms and reports to display information from the current tables and queries and maintain the database.
GO19_AC_COMP_GRADER_CAP1_Instructions.docx Grader - InstructionsAccess 2019 Project GO19_AC_COMP_GRADER_CAP1 - Front Range 1.0 Project Description: In this project, you will apply skills you practiced from the objectives in Access Chapters 4-10. You will update the current database for Frank Osei, the Vice President of Finance, as he manages the company’s growth. You will modify existing tables and create a new one to track part-time employee information. In addition, you will create queries to assist Frank as he monitors the company’s performance and requests that data be updated. You will also create forms and reports to display information from the current tables and queries and maintain the database. Steps to Perform: 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. Field Name Field Details Field Properties Empl ID ID given to each employee, primary key Field size=5 First Name Employee’s first name Field size=18 Last Name Employee’s last name Field size=24 Availability Preferred availability for scheduling Format=all capital letters Start Date First day of employment at FRS Short date input mask Hourly Wage Earnings per hour Required field Max Hours The max number of hours the employee wants to work in a week Number format, Must be <= to 30 include validation text must be less than or equal to 30 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 to="" 30="" include="" validation="" text="" must="" be="" less="" than="" or="" equal="" to="" 30="" 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="">