Shelly Cashman Access 365 | Modules 4-7: SAM Capstone Project A Banner Consulting

GETTING STARTED

1. Save the file SC_AC365_CS4-7A_FirstLastName_1.accdb as SC_AC365_CS4-7A_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_CS4-7A_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. Open the Employees table in Design View. Use the Input Mask Wizard to add 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. The input mask should store the values without symbols in the mask. (Hint: Some of these values might be the default input mask values.)

c. Save and close the table.

2. Use the Form Wizard to create a new form based on the Booths table 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. Open the ParticipantRegistration form in Design View and update the 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. Align the Phone and Status controls and their associated labels using Top alignment.

c. Align the LastName and Phone text boxes using Left alignment.

d. Align the LastName and Phone labels using Left alignment.

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. With the ParticipantRegistration form still open in Design View, add registration details to the form as follows:

a. With the Use Control Wizards button selected, add a subform to the form.

b. Use the Registrations table for the subform.

c. Select the RegistrationID, ParticipantID, RegistrationFee, PresentationFee, and GuestFee fields (in that order) to add to the subform.

d. Accept the default link (Show Registrations for each record in Participants using ParticipantID) to link the main form to the subform.

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

f. 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. With the ParticipantRegistration form still open in Design View, add a title to the form. Use Participant Registration Form as the title as shown in Figure 1. Save and close the form.

Figure 1: ParticipantRegistration Form in Design View

6. Open the EmployeeEntry form in Design View and provide a way to find an employee as follows:

a. With the Use Control Wizards button selected, 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. With the EmployeeEntry form still open in Design View, provide a quick way to close the form as follows:

a. Add a command button to the right side of the Form Footer section.

b. Choose the Close Form 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. With EmployeeEntry form still open in Design View, add a rectangle control to the 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. With the EmployeeEntry form still open in Design View, update the design as follows to use consistent formatting:

a. Use the Format Painter to 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 (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. Use the Label Wizard to 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. (Hint: These formatting options may be the default settings for your label.)

c. On the first line of the label, include the FirstName field, a space, and the LastName field.

d. On the second line of the label, include the Address field.

e. On the third line of the label, include the City field, a comma (,), a space, the State field, a space, and then the Postal field. 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. Open the PresentationsAndRegistrations report in Layout View. Open the Group, Sort, and Total pane, and then add an ascending sort order on the RegistrationID field within the PresentationID group. Save but do not close the report.

13. With the PresentationsAndRegistrations report still open in Layout View, select the PresentationFee 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 PresentationsAndRegistrations report still open in Layout View, apply conditional formatting to the values in the TotalFees column so that if the field value is greater than or equal to 275, the field's font color is set to Aqua Blue (9th column, 1st row in the Standard Colors palette) and the font is bold. Compare the report to Figure 6, and then save and close the PresentationsAndRegistrations report.

Figure 6: PresentationsAndRegistrations Report in Layout View

15. Open the EmployeeContact report in Design View and set the Record Source property to the EmployeeContact query to use the query's record for later report development. Save and close the EmployeeContact report.

16. Open the RegistrationsByState report in Design View, and add a grouping field to the report as follows to reorganize the records:

a. Use the Group, Sort, and Total pane to add the State field as a grouping field.

b. Select the State header and change the Repeat Section property value to Yes to repeat the state name on each page.

c. With the State header still selected, modify the Force New Page property to create a new page after every section (using the After Section option). Save the RegistrationsByState report, but do not close it.

17. With the RegistrationsByState report still open in Design View, add a field to the State group header 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 use a font size of 14 point and a font weight of Bold.

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.

18. With the RegistrationsByState report still open in Design View, add page numbers and dates to the 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

19. Open the PresentationCharts form in Design View. Update the tab control as follows to provide more meaningful information:

a. Change the caption of the first tab control using Participants as the new name.

b. Change the caption of the second tab control using Fees as the new name. Save but do not close the PresentationCharts form.

20. With the PresentationCharts form still open in Design View, display the pie chart in the first tab control. Modify the chart type so that it appears as a Bar chart to display more detailed data. Compare the chart on the first tab control to the chart in Figure 8. Save but do not close the PresentationCharts form.

Figure 8: PresentationCharts Form - Participants Tab in Design View

21. With the PresentationCharts form still open in Design View, display the second tab control. Insert a chart into the second tab control 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 for your chart.

d. Use the PresentationID field as the field for the series and use the SumOfPresentationFee field (which will be renamed SumofSumOfPresentationFee) for the data in the chart. (Hint: These fields should be assigned automatically to the correct areas in the Chart Wizard.)

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

22. Open the RegistrationsByParticipant report in Design View. Remove the Page Header and Footer from the report. Save but do not close the report.

23. With RegistrationsByParticipant report still open in Design View, add a control as follows 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. Change the label associated with the text box using Name as the new label name.

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.

24. With the RegistrationsByParticipant report still open in Design View, add registration details to the report as follows:

a. Make sure that the Use Control Wizards button is selected.

b. Add a subreport to the Details section at approximately the .5" mark on the horizontal ruler and the .5" mark on the vertical ruler.

c. Base your subreport on the RegistrationDetails query.

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

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

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

g. In Design View, delete the label associated with the subreport.

h. 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.

i. 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.