Shelly Cashman Access 365 | Modules 8-10: SAM Capstone Project A Banner Consulting

GETTING STARTED

1. Save the file SC_AC365_CS8-10A_FirstLastName_1.accdb as SC_AC365_CS8-10A_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_CS8-10A_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 PreviewReports macro in Design View. Change the View argument to Print Preview to display the EmployeeContact report in Print Preview. Save the change to the macro, and then close the macro.

2. Create a new macro with two submacros as follows to open frequently used tables:

a. Open Macro Design View and display the Action Catalog, if necessary.

b. Add the first submacro to the macro, using Open Participants Table as the name.

c. Use the OpenTable action to open the Participants table in Datasheet View and in Edit data mode.

d. Add a second submacro to the macro, using Open Registrations Table as the name.

e. In the second submacro, use the OpenTable action to open the Registrations table in Datasheet View and in Edit data mode.

f. Save the macro using OpenTables as the macro name. Confirm that your macro matches Figure 1, and then close the macro.

Figure 1: OpenTables Macro

3. Open the EarlyRegistrations table in Datasheet View and create a data macro as follows that makes sure the Paid amount is logical and accurate:

a. Create a Before Change data macro for the table.

b. Add an If statement that checks whether the Paid field value is greater than the TotalFees field value. If it is, the macro should set the value in the Paid field to the same value in the TotalFees field.

c. Add an Else If statement that checks whether the Paid field value is less than 0. If it is, the macro should set the value to 0.

d. Verify that the data macro matches the one shown in Figure 2. Save and close the macro, and then save and close the EarlyRegistrations table.

Figure 2: Data Macro for the Before Change Event

4. Create a form for accessing other database forms as follows:

a. Create a Navigation form using the Horizontal Tabs layout.

b. Add the Booths, Employees, and Sponsors Datasheet forms to the Navigation form in that order.

c. Change the form title using Datasheet Navigation Form as the new title.

d. Save the navigation form using DatasheetNavigation as the form name. Switch to Form View and confirm that your form matches Figure 3. Save and close the DatasheetNavigation form.

Figure 3: DatasheetNavigation Form in Form View

5. Open the Employees form in Datasheet View and create a UI macro for the form as follows:

a. Open the Property Sheet for the EmployeeID column.

b. Open the Macro Builder for the On Click event on the Event tab.

c. Create a macro that opens the EmployeeEntry form when a user selects a value in the EmployeeID column. Set a temporary variable using EN as the name and [EmployeeID] as the expression.

d. Add an OpenForm action using EmployeeEntry as the form name, [EmployeeID]=[TempVars]![EN] as the Where condition, and Dialog as the window mode.

e. The last action of the macro should remove the temporary variable. Confirm that your macro matches Figure 4. Save and close the macro and then save and close the form.

Figure 4: UI Macro for the On Click Event in the Employees Form

6. Open the Main Menu Navigation form in Layout View and add another form as follows:

a. Add the FormList form to the Main Menu Navigation form as the last horizontal tab.

b. Rename the FormList tab using Entry Forms as the new name.

c. Move the Employees tab so that it appears first in the list. Switch to Form View, confirm that the form matches Figure 5, and then save and close the form.

Figure 5: Main Menu Navigation Form in Form View

7. Open the DetailForms form in Design View and add a command button as follows to open the RegistrationDetails form:

a. With the Use Control Wizards button selected, add a command button to the form below the Open ParticipantDetails Form button.

b. Select Miscellaneous as the category and Run Macro as the action.

c. Select OpenForms.Open RegistrationDetails Form as the macro.

d. Select the Text option and enter Open RegistrationDetails Form as the text.

e. Name the command button using OpenRegistration as the name.

f. Position the left edge of the new button at the 2" mark on the horizontal ruler and the top edge at the 2.5" mark on the vertical ruler, as shown in Figure 6. Save the changes to the form but do not close it.

8. With the DetailForms form still open in Design View, format the command buttons as follows to create a more consistent layout:

a. Adjust the size of the three buttons To Widest.

b. Adjust the spacing of the buttons to Equal Vertical.

c. Align the buttons to the Left. Confirm that your form matches Figure 6. Save and close the DetailForms form.

Figure 6: DetailForms Form in Design View

9. Create a custom database property as follows to provide a way to note final changes to the database:

a. From the Info screen in Backstage View, open the Properties dialog box for the database. (Hint: Click the View and edit database properties link.)

b. Add a custom property with Final as the property name.

c. Select Date as the type.

d. Enter 05/13/2029 as the value. Confirm that your custom property matches the one shown in Figure 7, and then click OK. (Hint: Your database may contain other properties.)

Figure 7: Custom Tab in the Properties Dialog Box

10. Use the Access Options dialog box to set the Main Menu Navigation form as the display form for the database. Save the changes to the options. (Hint: When an Access dialog box appears indicating you must close and reopen the database, click OK.)

11. Open the Participants table in Design View to change a property as follows to help users enter data accurately:

a. Create a custom input mask for the ParticipantID field so that each field value consists of two uppercase letters and three numbers. (Hint: Use LL999 as the input mask.)

b. Save the changes to the table.

12. With the Participants table still open in Design View, create a single-field index on the LastName field. The index should allow duplicate values. Save the changes to the table design and then close the table.

13. Open the Employees table in Design View and create a multiple-field index as follows to improve record retrieval:

a. Use EmployeeName as the name of the index.

b. Use the EmployeeLast field, sorted in Ascending order, as the first field in the index.

c. Use the EmployeeFirst field, sorted in Ascending order, as the second field in the index.

d. Save the changes to the table design, and then close the table.

14. Open the EarlyRegistrations table in Design View to improve data-entry accuracy as follows:

a. Open the Property Sheet for the table.

b. Create a validation rule for the table requiring that the Paid field value is always less than or equal to the TotalFees field value. (Hint: Enter [Paid]=[TotalFees] as the validation rule.)

c. Enter Paid cannot exceed TotalFees as the validation text. Close the Property Sheet, save the changes to the table, and then close it. (Hint: When asked if you want to test the existing data, click No.)

15. Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named SingleOneColumnRightLabels and is saved automatically.)

16. Customize the Navigation Pane as follows to focus on objects used for event administration:

a. Switch to viewing database items in the Navigation Pane by the Administration custom category.

b. Add the BoothEntry form and the EmployeeEntry form to the Administration Forms group.

17. Add a new group to the Administration category as follows to list administration reports:

a. Use Administration Reports as the name for the new group.

b. If necessary, move the Administration Reports group so that it appears between the Administration Forms group and the Unassigned Objects group.

c. In the Navigation Pane, add the EmployeeContact report to the Administration Reports group. Confirm that the Navigation Pane matches Figure 8.

Figure 8: Administration Category in the Navigation Pane

18. Create a new query in SQL View based on the Presentations table as follows to list the presentations given at fundraising events:

a. Add all fields from the Presentations table to the new query using the asterisk (*) in the SELECT clause.

b. Be sure to end the SQL command with a semicolon (;).

c. Run the query and confirm that the results contain seven records. Save the query using PresentationList as the name, and then close the query.

19. Open the BoothList query in SQL View, and then add the ContactFname field to the SELECT clause after the BoothName field. Run the query and confirm that the results contain 14 records and five fields. Save and close the BoothList query.

20. Open the PartialPayments query in SQL View. The query is supposed to show the remaining payments for registration fees. Modify the query as follows:

a. Add a computed field to the SELECT clause after the Paid field that calculates the remaining registration payments by subtracting the Paid field value from the TotalFees field value. (Hint: Enter TotalFees-Paid as the computation.)

b. Add an AS clause to use Remaining as the name (alias) for this computed field.

c. Run the query and confirm that the results contain 13 records as shown in Figure 9. (Hint: Your records may be in a different order.) Save and close the PartialPayments query.

Figure 9: PartialPayments Query Results

21. Open the LowRegistration query in SQL View. Add a WHERE clause to the query to retrieve only those registrations where the RegistrationFee is less than 150. Run the query and confirm that the results contain six records. Save and close the LowRegistration query.

22. Open the ParticipantStates query in SQL View. The query currently selects participants in Ohio, but you also want to select participants in Wisconsin. Add an OR clause to the WHERE clause so that the query retrieves only those records in which the State field is equal to OH or WI. Run the query and confirm that the results contain seven records. Save and close the ParticipantStates query.

23. Open the HighRegistration query in SQL View. The query currently retrieves records where the RegistrationFee field equals 200, but you want to review records for $200 registration fees and $75 presentation fees. Add an AND clause to the WHERE clause in the query so that the query retrieves only those records in which the RegistrationFee field is equal to 200 and the PresentationFee field is equal to 75. Run the query and confirm that the results contain three records. Save and close the HighRegistration query.

24. Open the MichiganCount query in SQL View to count the number of participants in Michigan. Modify the SELECT clause to SELECT COUNT(ParticipantID) and add an AS clause to use StateCount as the alias of the column displaying the result. Run the query and confirm that the value in the StateCount column is 29. Save and close the MichiganCount query.

25. Open the JoinTables query in SQL View. Modify the query as follows to join tables and show one registration for each participant in the query results:

a. Add a WHERE clause that joins the Registrations table and the Participants table on the ParticipantID common field.

b. Qualify the field names in the WHERE clause.

c. Run the query and confirm that the results contain 37 records. Save and close the JoinTables query.

26. Open the SponsorList query in SQL View and clarify the contents of a column in the query results as follows:

a. Use an AS clause to add a caption to the SponsorName field in the SELECT clause.

b. Use FinancialSupporter as the caption for the SponsorName field.

c. Run the query, resize all columns to their best fit, and confirm that the second field in the query is displayed as FinancialSupporter. Save and close the SponsorList query.

27. Open the EmployeeList query in SQL View. By default, the query is sorted by the EmployeeID field value, but you prefer to list the records by employee last name. Modify the query to include an ORDER BY clause that sorts the records in ascending order by the EmployeeLast field. Run the query and confirm that the results match those shown in Figure 10. Save and close the EmployeeList query.

Figure 10: EmployeeList Query Results

28. Open the TotalParticipants query in SQL View. The query currently counts the number or participants in the Participants table. Modify the query as follows to count the participants by state:

a. Add the State field to the SELECT statement before the Count(ParticipantID) function.

b. Group the records by the State field.

c. Order the records in the State field in ascending order. Run the query and confirm that the results contain three records, one for each state of MI, OH, and WI. Save and close the TotalParticipants query.

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.