Shelly Cashman Access 365 | Modules 4-7: SAM Critical Thinking Capstone Project C Banner Consulting

GETTING STARTED

1. Save the file SC_AC365_CT_CS4-7C_FirstLastName_1.accdb as SC_AC365_CT_CS4-7C_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_AC365_CT_CS4-7C_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Banner Consulting is a nationwide consulting firm with headquarters in Ann Arbor, Michigan. The firm hosts community fundraising events throughout the year and encourages employees and their families to participate. You work in the Human Resources Department and help coordinate the events using an Access database to track data about participants, sponsors, fundraising booths, and registrations. Modify the Employees table to make it easier to enter phone numbers by adding an input mask to the Phone field using the following options:

a. Use the Phone Number input mask with the Input Mask format !(999) 000-0000 and the underscore (_) as the placeholder character.

b. Store the values without symbols in the input mask.

c. Save and close the table.

2. Use the Form Wizard to create a new form using the following options:

a. Select all fields in the Booths table.

b. Use the Columnar layout.

c. Use BoothEntry as the title of the form. View the BoothEntry form in Form View, and then save and close the form.

3. Update the ParticipantRegistration form as follows to add a missing field:

a. Display the fields for the Participants table and then add the Phone control to the form directly below the LastName control and label.

b. Top-align the Phone and Status controls and their associated labels.

c. Left-align the LastName and Phone text boxes.

d. Left-align the LastName and Phone labels.

e. Change the tab order so that users tab to the Phone control directly after the LastName control. Save but do not close the form.

4. Add missing registration details to the ParticipantRegistration form as follows:

a. Use a control wizard to add a subform based on the Registrations table to the form.

b. Add the RegistrationID, ParticipantID, RegistrationFee, PresentationFee, and GuestFee fields (in that order) to the subform.

c. Accept the default link to link the main form to the subform.

d. Save the subform using RegistrationDetails as the subform name.

e. If necessary, reposition the subform to match the approximate location shown in Figure 1. (Hint: The left edge of the subform is at 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 ParticipantRegistration form but do not close it.

5. Add Participant Registration Form as the title of the ParticipantRegistration form as shown in Figure 1. Save and close the form.

Figure 1: ParticipantRegistration Form in Design View

6. Modify the EmployeeEntry form as follows to provide a way to find an employee:

a. Use a control wizard to add a combo box to the right side of the Form Header section.

b. Choose to have the combo box get the values from the Employees table.

c. Select the EmployeeID and EmployeeLast fields (in that order) for the combo box.

d. Sort the records in ascending order by the EmployeeLast field.

e. Hide the key column and remember the value for later use.

f. Use Find Employee as the label for the combo box.

g. Resize and 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 6" mark on the horizontal ruler and the bottom edge of the control is at the 0.5" mark on the vertical ruler.) Save the changes to the EmployeeEntry form but do not close it.

7. Provide a quick way to close the EmployeeEntry form as follows:

a. Use a control wizard to add a command button to the right side of the Form Footer section.

b. Choose the appropriate action for the command button.

c. Display the text Close and no picture on the button.

d. Use CloseForm as the meaningful name for the command button.

e. Position the command button in the Form Footer section with the left edge at the 6" mark on the horizontal ruler, as shown in Figure 2. Save the EmployeeEntry form but do not close it.

8. Add a rectangle control to the EmployeeEntry form as follows to separate the employee information from the department information:

a. Add a rectangle control around the DepartmentID and DepartmentName controls.

b. Position the upper-left corner of the rectangle at approximately the 5.25" mark on the horizontal ruler and the 0.25" mark on the vertical ruler.

c. Position the lower-right corner of the rectangle at approximately the 8" mark on the horizontal ruler and the 1.1" mark on the vertical ruler. Save the EmployeeEntry form but do not close it.

9. Update the design of the EmployeeEntry form as follows to use consistent formatting:

a. Copy the formatting from the EmployeeID label to the other five labels in the Detail section of the form.

b. Change the font color of the six text boxes in the Detail section to Dark Blue (4th column, 1st row in the Standard Colors palette). Save the EmployeeEntry form, compare it to Figure 2, and close the form.

Figure 2: EmployeeEntry Form in Design View

10. Use the Report Wizard to create a multi-table report as follows to list participant fees:

a. Add the FirstName and LastName fields (in that order) from the Participants table.

b. Add the RegistrationFee, PresentationFee, and GuestFee fields (in that order) from the Registrations table to the report.

c. View the data by Participants with no additional grouping levels.

d. Sort the report in ascending order by RegistrationFee.

e. Use the Stepped layout and the Portrait orientation for the report.

f. Use ParticipantFees as the report title. Compare your report to Figure 3, and then save and close the report.

Figure 3: ParticipantFees Report in Print Preview

11. Create mailing labels for the Participants table using the following options:

a. Use the Avery C2160 label size, which has dimensions of 1 1/2" X 2 1/2" and three columns.

b. Use Arial font, 12 point font size, Normal font weight, and Black font color with no special font styles for the labels.

c. The first line of the label should show the recipient's first and last names separated by a space.

d. The second line of the label should show the recipient's address.

e. The third line of the label should show the city, state, and postal code with standard punctuation and spacing. Your label should match the label in Figure 4.

f. Sort the labels by the LastName field and then the FirstName field.

g. Use ParticipantLabels as the name for the labels report. Preview the mailing labels, confirming they match Figure 5. Save and close the ParticipantLabels report.

Figure 4: Participant Prototype Level

Figure 5: ParticipantLabels Report in Print Preview

12. Modify the PresentationsAndRegistrations report in Layout View to complete the report and make it easier to interpret as follows:

a. Sort the report in ascending order on the RegistrationID field within the PresentationID group.

b. Add subtotals and a grand total to the PresentationFee column using the Sum function.

c. Format the values in the TotalFees column so that if the field value is greater than or equal to 275, the field's value appears in Aqua Blue (9th column, 1st row in the Standard Colors palette) and bold. Compare the report to Figure 6, and then save and close the PresentationsAndRegistrations report.

Figure 6: PresentationsAndRegistrations Report in Layout View

13. Modify the EmployeeContact report to set the Record Source property to the EmployeeContact query to use the query's records for later report development. Save and close the EmployeeContact report.

14. Modify the RegistrationsByState report to add a grouping field as follows to reorganize the records:

a. Add the State field as a grouping field.

b. Change a property of the State header section to repeat the state name on each page.

c. Change another property of the State header section to force a new page after every section. Save the RegistrationsByState report, but do not close it.

15. Add a field to the State group header in the RegistrationsByState report as follows to display the state name:

a. Add the State field to the left side of the State header.

b. Delete the label associated with the State control.

c. Modify the State control to display 14-pt bold text.

d. Reposition the State control so that the left edge is at the left margin.

e. Resize the State control so that the right edge of the control is at the 1.5" mark on the horizontal ruler. Save the RegistrationsByState report, but do not close it.

16. Add page numbers and dates to the RegistrationsByState report as follows:

a. Add the Date to the report using the Short Date (e.g., 12/12/2029) format. Do not include the time in the report.

b. Add Page Numbers to the report using the Page N of M format at the Bottom of the page with the Right alignment option. Save the report and compare it to Figure 7. Close the RegistrationsByState report.

Figure 7: RegistrationsByState Report in Design View

17. Modify the PresentationCharts form to update the tab control as follows to provide more meaningful information:

a. Modify the first tab control to display Participants on the tab.

b. Modify the second tab control to display Fees on the tab. Save but do not close the PresentationCharts form.

18. Modify the PresentationCharts form to display a Bar chart instead of a Pie chart on the Participants tab so that the chart provides more detailed data. Compare the bar chart to Figure 8. Save but do not close the PresentationCharts form.

Figure 8: PresentationCharts Form - Participants Tab in Design View

19. Insert a chart into the second tab control on the PresentationCharts form as follows to compare fees:

a. Base the chart on the PresentationTotals query.

b. Use the PresentationID and SumOfPresentationFee fields (in that order) in the chart.

c. Use a Pie Chart as the chart type.

d. Use the default arrangement of the PresentationID field as the field for the series and the SumOfPresentationFee field for the data in the chart.

e. Use Total Fees by Presentation as the name of your chart.

f. Display a legend in the chart.

g. 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: PresentationCharts Form - Fees Tab in Design View

20. Remove the Page Header and Footer from the RegistrationsByParticipant report to simplify the report. Save but do not close the report.

21. Add a control as follows to the RegistrationsByParticipant report to display the full name of each participant:

a. Add a Text Box control to the ParticipantID header to the right of the ParticipantID control.

b. Enter the following expression in the Text Box control: =[FirstName]&" "&[LastName]

c. Reposition and resize the text box so that the left edge is at the 4" mark and the right edge is at the 6" mark on the horizontal ruler as shown in Figure 10.

d. Set the Border Style property of the text box to Transparent.

e. Use Name as the text for the label associated with the text box.

f. Reposition and resize the label as necessary so that all text in the label is visible and the right edge of the label is at the 3.75" mark on the horizontal ruler. Save the report but do not close it.

22. Add registration details to the RegistrationsByParticipant report as follows:

a. Use a control wizard to add a subreport to the Details section at approximately the .5" mark on the horizontal ruler and the .5" mark on the vertical ruler.

b. Base your subreport on the RegistrationDetails query.

c. Include the ParticipantID, RegistrationID, and RegistrationFee fields (in that order) in the subreport.

d. Accept the default link between the main form and subform.

e. Use the default name RegistrationDetails subreport as the name of the subreport.

f. Delete the label associated with the subreport.

g. Reposition the subreport so that the upper-left corner is at .5" mark on the horizontal ruler and the .5" mark on the vertical ruler. The bottom-right corner of the subreport is at the 1.5" mark on the vertical ruler and the 5.5" mark on the horizontal ruler.

h. If necessary, resize the Detail section to approximately 2" as shown in Figure 10.

Figure 10: RegistrationsByParticipant 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 website to submit your completed project.