Shelly Cashman Access 2019 | Modules 4-7: SAM Critical Thinking Capstone Project 1c Economic Development Conference Enhancing Forms and Reports GETTING STARTED Open the file SC_AC19_CT_CS4-7c_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

Shelly Cashman Access 2019 | Modules 4-7: SAM Critical Thinking Capstone Project 1c

Economic Development Conference

Enhancing Forms and Reports

GETTING STARTED

• Open the file SC_AC19_CT_CS4-7c_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as SC_AC19_CT_CS4-7c_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. The Economic Development Professional Organization hosts an annual conference for its members, college students, and other interested persons to share new ideas, discuss problems, and brainstorm solutions. You are a conference coordinator responsible for registration. Open the Advertisers table and add an input mask to the ContactPhone field using the following options:
    
   1. Use the input mask format !(999) 000-0000 and the underscore (_) as the placeholder character.
   2. The input mask should store the values without symbols in the mask.
   3. Save and close the table.
2. Create a new form based on the Exhibitors table using the following options:
    
   1. Select all fields in the Exhibitors table.
   2. Use the Columnar layout.
   3. Use Exhibitors Information as the title of the form.
      
       View the Exhibitors Information form in Form View, and then save and close the form.
3. Open the Attendee Registration Form and update it as follows:
    
   1. Add the CellPhone control and label to the form directly below the LastName control and label.
   2. Align the CellPhone and Status controls and their associated labels using the Top option.
   3. Align the LastName and CellPhone controls using the Left option.
   4. Align the LastName and CellPhone labels using the Left option.
   5. Change the tab order so that users tab to the CellPhone control directly after the LastName control.
      
      Save but do not close the form.
4. With the Attendee Registration Form still open, ensure that the Use Control Wizards button is selected. Add a subform with the following options:
    
   1. Use the Registration table for the subform.
   2. Select the RegistrationID, AttendeeID, RegistrationFee, WorkshopFee, and GuestFee fields (in that order) from the Registration table to add to the subform.
   3. Accept the default link ("Show Registration for each record in Attendees using AttendeeID") to link the main form to the subform.
   4. Save the subform using Registration Details as the subform name.
   5. If necessary, reposition the subform to match the approximate location shown in Figure 1. (Hint: The left edge of the subform is at the 0.5" mark on the horizontal ruler, and the top of the subform label is at the 2" mark on the vertical ruler.)
      
      Save the Attendee Registration Form, but do not close it.
5. With the Attendee Registration Form still open, add the title Attendee Registration Detail Form to the form. Save and close the form.

Figure 1: Attendee Registration Form in Design View

6. Open the Advertiser Data Entry Form and ensure that the Use Control Wizards button is selected. Add a combo box to the right side of the Form Header section as described below:
    
   1. The combo box should find a record in the form.
   2. The AdvertiserID and AdvertiserName fields (in that order) should be selected for the combo box.
   3. The key column should be hidden.
   4. Use Find Advertiser as the label for the combo box.
   5. Reposition the combo box control and label to the approximate location shown in Figure 2. (Hint: The left edge of the combo box control is at the 4" mark on the horizontal ruler, and the bottom edge of the control is at the 1" mark on the vertical ruler.)
      
      Save the changes to the Advertiser Data Entry Form, but do not close it.
7. With the Advertiser Data Entry Form still open, add a command button to the right side of the Form Footer section with these options:
    
   1. The command button action should close the form.
   2. The button should display the text Close and no picture.
   3. Use Close Form as the meaningful name for the command button.
   4. The command button should be positioned in the Form Footer section with the left edge at approximately the 4" mark on the horizontal ruler, as shown in Figure 2.
      
      Save the Advertiser Data Entry Form, but do not close it.
8. With the Advertiser Data Entry Form still open, add a rectangle control to enclose the ContactFName, ContactLName, and ContactPhone labels and controls as described below:
    
   1. The upper-left corner of the rectangle should be located at approximately the 1" mark on the vertical ruler and the 3" mark on the horizontal ruler.
   2. The lower-right corner of the rectangle should be located at approximately the 2.1" mark on the vertical ruler and the 5.5" mark on the horizontal ruler.
      
      Save the Advertiser Data Entry Form, but do not close it.
9. With the Advertiser Data Entry Form still open, make the following formatting changes:
    
   1. Use the Format Painter to copy the formatting from the AdvertiserID label to the other five labels in the Detail section of the form.
   2. In the Detail section of the form, change the fore color of the AdvertiserID, AdvertiserName, Cost, ContactFName, ContactLName, and ContactPhone controls to Blue (last row, 8th column in the Standard Colors palette).
      
      Save the Advertiser Data Entry Form, compare the form to Figure 2, and then close the form.

Figure 2: Advertiser Data Entry Form in Design View

10. Create a multi-table report as described below:
     
    1. Add the FirstName and LastName fields (in that order) from the Attendees table.
    2. Add the RegistrationFee, WorkshopFee, and GuestFee (in that order) from the Registration table to the report.
    3. The data should be viewed by the Attendees field with no additional grouping levels.
    4. Sort the report in ascending order by the RegistrationFee field.
    5. Use the Stepped layout and the Portrait orientation for the report.
    6. Use Registered Attendees as the report title.
       
       Compare your report to Figure 3, and then save and close the report.

Figure 3: Registered Attendees in Report View

11. Create mailing labels for the Attendees table as follows:
     
    1. Use the Avery C2160 label size, which has dimensions of 1 1/2" x 2 1/2" and three columns.
    2. Use Arial font, a 12-point font size, Normal font weight, and Black font color with no special font styles for the labels.
    3. On the first line of the label, include the FirstName field, a space, and the LastName field.
    4. On the second line of the label, include the Address field.
    5. On the third line of the label, include the City field, a comma (,), a space, the State field, a space, and then the ZIP field. Your label should match the label in Figure 4.
    6. Sort the labels by the LastName field and then the FirstName field.
    7. Use Attendee Mailing Labels as the name for the labels report. (Hint: If you receive a message indicating all information may not be displayed, click OK.) Preview the mailing labels and confirm they match Figure 5. Save and close the Attendee Mailing Labels report.

Figure 4: Attendee Prototype Label

Figure 5: Attendee Mailing Labels in Print Preview View

12. Open the Registrations by Workshop report and add an ascending sort order on the RegistrationID field within the WorkshopID group. Save but do not close the report.
13. With the Registrations by Workshop report still open, select the WorkshopFee column, and then add subtotals and a grand total to the column using the Sum function. Save but do not close the report.
14. With the Registrations by Workshop report still open, apply conditional formatting to the values in the ConferenceFees column so that if the field value is greater than or equal to 275, the field's font color is set to Green (last row, 6th column of the Standard Colors palette) and the font is bold. Compare the report to Figure 6, and then save and close the Registrations by Workshop report.

Figure 6: Registrations by Workshop report in Layout View

15. Open the Advertisers Contact Report and set the Record Source property to the Advertisers Contact Query. Save and close the Advertisers Contact Report.
16. Open the Registrations by State report and add a grouping field to the report as described below:
    1. Add the State field as a grouping field.
        
    2. Change the Repeat Section property value to Yes for the State header.
    3. With the State header still selected, modify the Force New Page property so that a new page should be created after every section (using the After Section option).
       
       Save the Registrations by State report, but do not close it.
17. With the Registrations by State report still open, add a field to the State group header as described below:
     
    1. Add the State field to the State header as shown in Figure 7.
    2. Delete the label associated with the State control.
    3. Select the State control and modify it to display with a font size of 14 points and in bold.
    4. Reposition the State control so that the left edge is at the left margin.
    5. Resize the State control so that the right edge of the control is at the 1.5" mark on the horizontal ruler.
       
       Save the Registrations by State report, but do not close it.
18. With the Registrations by State report still open, add page numbers and dates to the report as described below:
     
    1. Add the date to the report using the Short Date (e.g., 12/12/2019) format. Do not include the time in the report.
    2. Add page numbers to the report, using the Page N of M format, at the bottom of the page, with the Right alignment option selected.
       
       Save the report and compare it to Figure 7. Close the Registrations by State report.

Figure 7: Registrations by State report in Design View

19. Open the Workshop Charts form. In the Tab control, make the following updates:
     
    1. Change the name of the first tab control, using Attendees as the new name.
    2. Change the name of the second tab control, using Fees as the new name.
       
       Save but do not close the Workshop Charts form.
20. With the Workshop Charts form still open, switch to viewing the Pie chart in the first tab control. Modify the chart type so that it appears as a Bar chart. Compare the chart on the first tab control to the chart in Figure 8. Save but do not close the Workshop Charts form.

Figure 8: Workshop Charts Form – Attendees Tab in Design View

21. With the Workshop Charts form still open, switch to viewing the second tab control. Insert a chart into the second tab control as described below:
     
    1. Base the chart on the Workshop Totals query.
    2. Include the WorkshopID and SumOfWorkshopFee fields (in that order) in your chart.
    3. Select 3-D Pie Chart as the chart type for your chart.
    4. The WorkshopID field should be the field for the series, and the SumOfWorkshopFee field (which will be renamed SumofSumOfWorkshopFee) should be the field for the data in the chart. (Hint: These fields should be assigned automatically to the correct areas in the Chart Wizard.)
    5. Use Total Fees by Workshop as the name of your chart.
    6. Display a legend in the chart.
    7. If necessary, resize the chart object to the same size as the tab control, as shown in Figure 9.
       
       Save the form, compare it to Figure 9, and then close the form.

Figure 9: Workshop Charts Form – Fees Tab in Design View

22. Open the Attendee Registration Report and remove the Page Header and Footer sections from the report. Save but do not close the report.
23. With the Attendee Registration Report still open, add a text box control to the AttendeeID header as described below:
     
    1. Add the text box control to the AttendeeID header in the approximate location shown in Figure 10. (Hint: The left edge of the control should align with the left edge of the AttendeeID control.)
    2. Enter the following expression into the text box control: =[FirstName]&" "&[LastName]
    3. Reposition and resize the text box control so that the left edge is at the 1" mark and the right edge is at the 3" mark on the horizontal ruler, as shown in Figure 10.
    4. Set the Border Style property of the text box control to Transparent.
    5. Change the label associated with the text box, using Name as the new label name.
       Reposition and resize the label as necessary so that all text in the label is visible and the left edge of the label is at the 0.1" mark on the horizontal ruler.
       
       Save the report, but do not close it.
24. With the Attendee Registration Report still open, ensure that the Use Control Wizards button is selected. Insert a subreport in the Detail section of the report as described below:
     
    1. Add the subreport to the Detail section at approximately the 0.5" mark on the horizontal ruler and the 0.5" mark on the vertical ruler.
    2. Base your subreport on the Attendees and Registration query.
    3. Include the AttendeeID, RegistrationID, and RegistrationFee fields (in that order) in the subreport.
    4. Accept the default link ("Show Attendees and Registration for each record in Attendees using AttendeeID").
    5. Use the default name Attendees and Registration subreport as the name of the subreport.
    6. In Design View, delete the label associated with the subreport.
    7. Reposition the subreport so that the upper-left corner is at the 0.5" mark on the horizontal ruler and the 0.5" mark on the vertical ruler, and the lower-right edge of the subreport is at the 1.5" mark on the vertical ruler and the 5.2" mark on the horizontal ruler.
    8. If necessary, resize the Detail section to the approximate size shown in Figure 10.

Figure 10: Attendee Registration Report in Design View

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the SAM website to submit your completed project.