Shelly Cashman Access 365 | Modules 1-3: SAM Capstone Project A Banner Consulting

GETTING STARTED

1. Save the file SC_AC365_CS1-3A_FirstLastName_1.accdb as SC_AC365_CS1-3A_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. To complete this Project, you will also need the following files:

Support_AC365_CS1-3A_Employees.xlsx

3. With the file SC_AC365_CS1-3A_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. Create a new table in Datasheet View with the following options to store data about employees who supervise the fundraising events:

a. Rename the default primary key ID field to EmployeeID and change the data type to Short Text. (Hint: EmployeeID should remain the primary key.)

b. Add a new field with the name EmployeeFirst and the Short Text data type to store employee first names.

c. Add a third field to the table with the name EmployeeLast and the Short Text data type to store employee last names.

d. Add a fourth field to the table with the name Department and the Short Text data type to indicate the employee's department. Save the table with the name Employees and close the table.

2. Add data to the Employees table as follows:

a. Use the Import Spreadsheet Wizard to import the data from the Support_AC365_CS1-3A_Employees.xlsx file and append it to the Employees table. Do not save the import steps.

b. Open the Employees table in Datasheet View to confirm that it contains 15 records and then close the table.

3. Create a new table in Design View with the following options to store data about Banner departments and the funds they allocate to fundraising events:

a. Add a field with the name DepartmentID and the Short Text data type. Change the field size to 4.

b. Set DepartmentID as the primary key for the table.

c. Add a field with the name DepartmentName and the Short Text data type to store department names.

d. Add a third field with the name Budget and the Currency data type to store budget amounts. Save the table with the name Departments and switch to Datasheet View.

4. With the Departments table open in Datasheet View, add the record shown in Table 1. Resize the DepartmentName field to display the entire field value. Save the changes and close the Departments table.

Table 1: New Record for the Departments Table

DepartmentID | DepartmentName | Budget D100 | Administration | $15,000

5. Open the Participants table in Datasheet View and delete the record for the participant with a ParticipantID of PA004 (Antoinette Pugh) since that participant is no longer involved in the fundraising programs.

6. Switch to Design View for the Participants table and make the following changes to clarify the purpose of one field and add another:

a. Change the name of the Street field using the more accurate Address as the new field name.

b. Add a new field to the end of the table with the name Phone and the Short Text data type to store participants' phone numbers. Save the changes but do not close the table.

7. With the Participants table still open in Design View, add a new field as follows to indicate each participant's status:

a. Add a field with the name Status to the end of the table.

b. Select Lookup Wizard at the data type.

c. Select the option to type in your own values.

d. Type the following three values (in the order shown) as the list of possible values for the field: Employee, Guest, and Student. Limit the field values to only the items in the list and do not allow multiple values for the field. Save the changes to the table and close it.

8. Open the Booths table in Design View and perform the following tasks to refine the fields in the table:

a. Make the BoothID field the primary key of the table.

b. Change the field size of the BoothID field to 4.

c. Change the data type of the BoothCost field to Currency with 0 decimal places.

d. Add the text Basic cost for a 10 x 10 booth as the description for the BoothCost field. Save the changes to the table and then close it. (Hint: Because a field size was reduced, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)

9. Open the Sponsors table in Datasheet View and make the table display more meaningful information as follows:

a. Sort the records in ascending order by SponsorName.

b. Add the Total row to the datasheet and calculate the Sum for the Amount field.

c. Resize the Amount field to its best fit to display the entire total. Save the changes to the table and close it.

10. Open the Registrations table in Design View and improve data-entry accuracy as follows:

a. Enter a Validation Rule property for the RegistrationFee field to ensure that its values are greater than or equal to 100.

b. Enter the text Must be greater than or equal to $100 in the Validation Text property box as the message to display to users. Save the changes to the Registrations table and close it. (Hint: Because a validation rule was added, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)

11. Use the Form Wizard to create a data-entry form for the Employees table with the following options:

a. Include the EmployeeID, EmployeeFirst, EmployeeLast, and Department fields.

b. Select the Columnar layout.

c. Assign the title EmployeeEntry to the form. View the form and then close it.

12. Create a split form for the Registrations table to display a simple form with a single record and a datasheet of all the records in the table. Save the form using RegistrationSplit as the form name, and then close the form.

13. Use the Report Wizard to create a report for the Participants table with the following options to provide location information for participants:

a. Include the LastName, FirstName, City, and State fields.

b. Do not add any grouping levels.

c. Sort the records in ascending order by LastName.

d. Select the Tabular layout and the Portrait orientation.

e. Assign the title ParticipantLocations to the report. View the report and then close it.

14. Use the Simple Query Wizard to create a query based on the Participants table with the following options to list participant contact information:

a. Include the ParticipantID, FirstName, LastName, and Phone fields in that order.

b. Save the query using ParticipantContacts as the query name. View the query results and then close the query.

15. Create a query in Design View for the Participants table with the following options to list participants from Ann Arbor:

a. Include the LastName, FirstName, City, and State fields in the query design grid.

b. Sort the records in ascending order by LastName.

c. Add criteria to select only those records where Ann Arbor is the City.

d. Hide the State field values because the state is implied by the city.

e. Save the query using LocalParticipants as the query name. Open the query in Datasheet View to display the results and then close it.

16. Open the GuestAndEvent query in Design View and make the following changes to the query to list participants who are not being charged a guest fee and an event fee:

a. Add the RegistrationFee field to the query design grid immediately following the ParticipantID field.

b. Add criteria to select only those records where the GuestFee field value equals 0 and the EventFee equals 0. Open the query in Datasheet View and confirm that 16 records appear in the GuestAndEvent query results. Save and close the query.

17. Create a query in Design View based on the Participants and Registrations tables with the following options to list registered participants:

a. Add the Participants table and the Registrations table to the Design window. Access should automatically join the tables by the common field ParticipantID.

b. Add the FirstName and LastName fields from the Participants table to the query design grid in that order.

c. Add the RegistrationID, RegistrationFee, and EventFee fields from the Registrations table in that order.

d. Save the query using RegisteredParticipants as the query name. Open the query in Datasheet View to display the results and then close it.

18. Open the OrCities query in Design View and make the following changes to list participants from Grand Rapids or Kalamazoo:

a. Move the City field so that it is the first field in the query design grid.

b. Add criteria to select only those records where the value in the City field is Grand Rapids or Kalamazoo.

c. Sort the records in ascending order by City and then by LastName. Open the query in Datasheet View and confirm that it matches Figure 1. Save and close the query.

Figure 1: OrCities Query Results

19. Use the Crosstab Query Wizard as follows to create a crosstab query based on the Participants table to show how many participants are from each city and state:

a. Use the State field for the row headings.

b. Use the City field for the column headings.

c. Use a Count of the ParticipantID field as the calculated value for each row and column intersection.

d. Save the query using Participants_Crosstab as the query name. View the query and then close it.

20. Open the ParticipantName query in Design View and add criteria to select only those records where the LastName field value begins with P followed by any other letters. Save the changes to the query. Display the query in Datasheet View, confirm that four records appear in the query results, and then close it.

21. Open the StateParticipants query in Design View. Add parameter criteria to the State field to replace the current "MI" criteria. The new parameter criteria should prompt the user with Enter desired state as the text so that users can specify the participants' state. Save the query, and then display it in Datasheet View. Enter OH when prompted. Confirm that five records appear in the query results and then close the query.

22. Open the AllFees query in Design View. Modify the query by creating a calculated field that sums the total of RegistrationFee, GuestFee, and EventFee to display the total fees. Use TotalFees as the name of the calculated field. Save the query, open it in Datasheet View to display the results, and then close it.

23. Open the CombinedFees query in Design View. Modify the query by sorting the records in descending order by the calculated field TopFees and display only the top 25% of the records. Save the query. Display the query in Datasheet View, confirm that 16 records appear in the query results, and then close it.

24. Open the SortedCities query in Design View. Modify the query as follows to display each city only once:

a. Sort the City field in ascending order.

b. Add the Total row to the query design grid.

c. Group by the City field so that each city appears only once. Accept the default for the State field. Save the query. Display the query in Datasheet View, confirm that six records appear in the query results, and then close it.

25. Create a query in Design View for the Registrations table with the following options to display the totals for each type of fee:

a. Add the RegistrationFee, GuestFee, and EventFee fields to the query design grid.

b. Add the Total row to the query design grid.

c. Calculate the Sum of each of the three fields.

d. Save the query using AggregateFees as the query name. Open the query in Datasheet View, confirm that it matches Figure 2, and then close it.

Figure 2: AggregateFees Query Results

26. Most participants are employees of Banner Consulting. Use an Update query to update the values in the Status field of the Participants table to Employee for all records. Run the query, save it using UpdateStatus as the name of the query, and then close it.

27. Open the Participants table in Datasheet View and change the Status value for the participant with an ParticipantID of CM001 (Maribel Carballo) to Student to update the data. Close the table.

28. The NewSponsors table contains records that should be added to the Sponsors table. Create an Append query with the following options to add the records:

a. Select all the fields from the NewSponsors table in the same order as they appear in the field list. Select the fields individually for the query design grid; do not use the (*) asterisk.

b. Select Sponsors as the destination table in the Append dialog box.

c. Save the query using AppendSponsors as the name, run it, and then close it. The query should append 10 records.

29. Participants who paid the highest registration fees are often willing to donate additional funds. Create new table for these participants using a Make Table query as follows:

a. Select all fields from the Registrations table in the same order as they appear in the field list. Select the fields individually for the query design grid; do not use the (*) asterisk.

b. Add criteria to select only those records where the RegistrationFee field value equals 200, which is the highest registration fee.

c. In the Make Table dialog box, assign the name PotentialDonors to the new table.

d. Save the query using HighRegistration as the query name, run it, and then close the query. The new PotentialDonors table should contain 12 records.

30. Create a Delete query for the Booths table with the following options to remove a booth for an organization that is no longer involved with the fundraising programs:

a. Select the BoothName field from the Booths table.

b. Add criteria to delete only those records where Education Fund is the BoothName.

c. Save the query using DeleteBooth as the query name, run it, and then close it. The query should delete one record.

31. Open the Relationships window and add the Participants table and the Registrations table to the Relationships window. Create a relationship between the ParticipantID field in the Participants table and the ParticipantID field in the Registrations table. Make the relationship enforce referential integrity. Save the relationship and close the window.

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.