Shelly Cashman Access 365 | Modules 1-3: SAM Critical Thinking Capstone Project C Banner Consulting
CREATING TABLES, QUERIES, FORMS, AND REPORTS
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_CT_CS1-3C_FirstLastName_1.accdb as SC_AC365_CT_CS1-3C_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_CT_CS1-3C_Employees.xlsx
3. With the file SC_AC365_CT_CS1-3C_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 with the following options to store data about employees who supervise the fundraising events:
a. Use EmployeeID as the name of the primary key field and assign it the Short Text data type.
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 using Employees as the name and close it.
2. Add data to the Employees table as follows:
a. Import the data from the Excel workbook file Support_AC365_CT_CS1-3C_Employees.xlsx and append the data to the Employees table. Do not save the import steps.
b. Confirm that the Employees table contains 15 records and then close the table.
3. Create a new table 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. Use 4 as the field size.
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.
e. Use Departments as the name of the new table.
4. Add the record shown in Table 1 to the Departments table. 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. In the Participants table, delete the record for the participant PA004 (Antoinette Pugh) since she is no longer involved in the fundraising programs.
6. Make the following changes to the Participants table 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. Add a new field to the Participants table as follows to indicate each participant's status:
a. Add a field with the name Status to the end of the table.
b. Use Lookup Wizard as the data type.
c. Select the option to type in your own values.
d. Type Employee, Guest, and Student (in the order shown) as the list of possible values for the field. Limit the field values to the items in the list and do not allow multiple values for the field. Save the changes to the table and close it.
8. Refine the fields in the Booths table as follows:
a. Make the BoothID field the primary key of the table.
b. Use 4 as the field size of the BoothID field.
c. Change the data type of the BoothCost field to Currency with 0 decimal places.
d. Use 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: Although you reduced a field size, the data is still valid.)
9. Modify the Sponsors table datasheet to make the table display more meaningful information as follows:
a. Sort the records in alphabetic order by the name of the sponsor.
b. Add the Total row to the datasheet and calculate the sum of the sponsor amounts.
c. Resize the Amount field to its best fit to display the entire total. Save the changes to the table and close it.
10. Improve data-entry accuracy in the Registrations table as follows:
a. Enter a validation rule for the RegistrationFee field to ensure that its values are greater than or equal to 100.
b. Use Must be greater than or equal to $100 as the validation text to display to users. Save the changes to the Registrations table and close it. (Hint: Although you added a validation rule, the data is still valid.)
11. Create a form based on 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. 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. 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 another query based on the Participants table with the following options to list participants from Ann Arbor:
a. Include the LastName, FirstName, City, and State fields in that order.
b. Sort the records in ascending order by LastName.
c. Select only participants who live in Ann Arbor.
d. Hide the State field values because the state is implied by the city.
e. Save the query using LocalParticipants as the query name. Display the query results and then close the query.
16. Modify the GuestAndEvent query as follows to list participants who are not being charged a guest fee and an event fee:
a. Add the RegistrationFee field to the query so it appears immediately after the ParticipantID field.
b. Select only those records with a GuestFee of 0 and an EventFee of 0. Confirm that 16 records appear in the query results, and then save and close the query.
17. Create a query with the following options to list registered participants:
a. Add the Participants table and the Registrations table to the query. 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 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. Display the query results and then close the query.
18. Modify the OrCities query as follows 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. Select only records for participants in Grand Rapids or Kalamazoo.
c. Sort the records in ascending order by City and then by LastName. Confirm that the query results match Figure 1. Save and close the query.
Figure 1: OrCities Query Results
19. Create a crosstab query based on the Participants table as follows to show how many participants are from each city and state:
a. Display state names in the row headings.
b. Display city names in the column headings.
c. Display the count of the values in the ParticipantID field in each row and column intersection.
d. Save the query using Participants_Crosstab as the query name. View the query and then close it.
20. Modify the ParticipantName query and add criteria to select only those records where the participant's last name begins with P followed by any other letters. Save the changes to the query. Confirm that four records appear in the query results, and then close the query.
21. Modify the StateParticipants query by adding parameter criteria to the State field to replace the current criterion. Use Enter desired state as the text that prompts users to specify the participants' state. Save and run the query, entering OH when prompted. Confirm that five records appear in the query results and then close the query.
22. Modify the AllFees 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, display the results, and then close the query.
23. Modify the CombinedFees query by sorting the records in descending order by the calculated field TopFees and displaying only the top 25% of the records. Save and run the query to confirm that 16 records appear in the query results, and then close the query.
24. Modify the SortedCities 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 and run the query. Confirm that six records appear in the query results, and then close the query.
25. Create a query 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.
b. Add the Total row to the query design grid.
c. Calculate the sum of each field.
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. In the Participants table, change the status of ParticipantID 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.
b. Use Sponsors as the destination table.
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 a 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.
b. Add criteria to select only records with a RegistrationFee field value of 200, which is the highest registration fee.
c. 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 with Education Fund as 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. 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 Relationships 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.