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

GETTING STARTED

1. Save the file SC_AC365_CT_CS8-10C_FirstLastName_1.accdb as SC_AC365_CT_CS8-10C_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_CS8-10C_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 PreviewReports macro so that it opens the EmployeeContact report in Print Preview. Save the change to the macro, and then close it.

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

a. Add a submacro named Open Participants Table to the macro.

b. Set the submacro to open the Participants table for editing in Datasheet View.

c. Add a second submacro named Open Registrations Table to the macro.

d. Set the second submacro to open the Registrations table for editing in Datasheet View.

e. 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. Create a data macro for the EarlyRegistrations table as follows that makes sure the Paid amount is logical and accurate:

a. Choose a data macro for the table that triggers an event that occurs after the user has changed the data but before the change is actually made in the database.

b. Add a 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 a 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 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 with horizontal tabs.

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

c. Use Datasheet Navigation Form as the form title.

d. Save the navigation form using DatasheetNavigation as the name. Confirm that your form matches Figure 3 in Form view. Save and close the DatasheetNavigation form.

Figure 3: DatasheetNavigation Form in Form View

5. Create a UI macro for the Employees form as follows that opens the EmployeeEntry form when a user selects a value in the EmployeeID column:

a. Open the Property Sheet for the EmployeeID column.

b. Open the Macro Builder for the On Click event.

c. Set a temporary variable using EN as the name and [EmployeeID] as the expression.

d. Add an action that opens the EmployeeEntry form as a dialog window and displays only the record the user selected.

e. Remove the temporary variable in the last action of the macro. 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. Add another form to the Main Menu Navigation form as follows:

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

b. Use Entry Forms as the name of the new tab.

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. Add a command button as follows to the DetailForms form to open the RegistrationDetails form:

a. Use a control wizard to add a command button to the form below the Open ParticipantDetails Form button.

b. Choose to run the OpenForms.Open RegistrationDetails Form macro.

c. Use Open RegistrationDetails Form as the text to display on the command button.

d. Use OpenRegistration as the name of the command button.

e. 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. Format the command buttons on the DetailForms form as follows to create a more consistent layout:

a. Adjust the size of the three buttons to the width of the widest button.

b. Adjust the spacing to create equal vertical space between the buttons.

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. Open the Properties dialog box for the database.

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

c. Enter 05/13/2029 as the value.

d. Select the appropriate type for the value. Confirm that your custom property matches the one shown in Figure 7, and then accept the custom property changes.

Figure 7: Custom Tab in the Properties Dialog Box

10. Set an Access option to display the Main Menu Navigation form when the current database opens, and then save your changes.

11. Change a property in the Participants table 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 required uppercase letters and three optional numbers.

b. Save the change to the table.

12. Create a single-field index on the LastName field in the Participants table. The index should allow duplicate values. Save the change to the table and then close it.

13. Create a multiple-field index for the Employees table as follows to improve record retrieval:

a. Use EmployeeName as the name of the index.

b. Sort the EmployeeName index first by the EmployeeLast field in Ascending order.

c. Sort the index next by the EmployeeFirst field in Ascending order.

d. Save the changes to the Employees table, and then close it.

14. Improve data-entry accuracy in the EarlyRegistrations table 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.

c. Enter Paid cannot exceed TotalFees as the validation text. Close the Property Sheet, save the changes to the table without testing the existing data, and then close the table.

15. Create a blank form based on the 1 Right application part using the default form name.

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

b. Add the BoothEntry and EmployeeEntry forms 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 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 appropriate clause and ending the SQL command with a semicolon (;).

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

19. Update the BoothList query in SQL View so that the ContactFname field appears 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. Modify the PartialPayments query in SQL View as follows to show the remaining payments for registration fees:

a. Add a computed field after the Paid field that calculates the remaining registration payments by subtracting the Paid field value from the TotalFees field value.

b. Add a clause to display Remaining as the alias for the 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. Modify the LowRegistration query in SQL View to include criteria that retrieves 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. Modify the ParticipantStates query in SQL View to expand the selection criteria. The query currently selects participants in Ohio, but you also want to select participants in Wisconsin. Add a clause to the WHERE clause so that the query retrieves only those records in which the State field value is OH or WI. Run the query and confirm the results contain seven records. Save and close the ParticipantStates query.

23. Modify the HighRegistration query in SQL View to narrow the selection. 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 a clause to the WHERE clause so that the query retrieves only those records in which the RegistrationFee field value is 200 and the PresentationFee field value is 75. Run the query and confirm that the results contain three records. Save and close the HighRegistration query.

24. Modify the MichiganCount query in SQL View to change the results as follows to display the number of participants in Michigan:

a. Count the number of participants in Michigan by participant ID.

b. Add a clause to use StateCount as the alias of the column displaying the result.

c. Run the query and confirm that the value in the StateCount column is 29. Save and close the MichiganCount query.

25. Modify the JoinTables query in SQL View 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. Modify the SponsorList query in SQL View to clarify the contents of a column in the query results as follows:

a. Add a clause to use FinancialSupporter as the caption for the SponsorName field.

b. 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. Add a 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. Modify the TotalParticipants query in SQL View 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. Sort the records in the State field in ascending order. Run the query and confirm that the results contain three records, one for each states 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.