New Perspectives Access 365 | Module 3: End of Module Project 1 Bowles College
CREATING AND IMPROVING QUERIES
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_EOM3-1_FirstLastName_1.accdb as NP_AC365_EOM3-1_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 NP_AC365_EOM3-1_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. As a student affairs coordinator at Bowles College, you have been developing an Access database application to maintain information about student groups. You need to update data and create queries to answer questions about the groups and their advisors. Modify the Groups table in Datasheet View to update data. In the record with the GroupID value of SG-106 for the Astronomy Society, insert 40 for the Members value and Public for the Office value.
2. With the Groups table still open in Datasheet View, apply the last filter (the GroupType field is equal to 1) to display only those records with a GroupType of 1, which are Academic groups. Sort the records in ascending order by the GroupName field and then save and close the Groups table.
3. Open the Students table in Datasheet View and sort the records in descending order by BaseRate to list students by the base hourly rate they are paid to coordinate the group. Change the font size to 14, use Cambria as the font, and apply an alternate row color of Blue, Accent 1, Lighter 80% (5th column, 2nd row in the Theme Colors palette) to make the datasheet easier to read. Save and close the Students table.
4. Open the AdvisorList query in Datasheet View to correct an error. For Alpha Phi (record 2), change the AdvisorID value to 22. Note that the AdvisorFirst value updates to Binh and the AdvisorLast value updates to Vang due to the relationship between the Advisors and Groups tables.
5. Display the AdvisorList query in Design View and add a criterion to the Members field to select only those groups with more than 30 members. Save and display the AdvisorList query in Datasheet View as shown in Figure 1 and then close it.
Figure 1: AdvisorList Query in Datasheet View
6. Create a new query in Query Design View using the Groups and Students tables to list the student coordinators for each group. Add the GroupID and GroupName fields from the Groups table and the FirstName and LastName fields from the Students table to the query grid, in that order. Add an ascending sort order to the GroupName field. Save the query with the name GroupCoordinators, display the query in Datasheet View as shown in Figure 2, and then close it.
Figure 2: GroupCoordinators Query in Datasheet View
7. Modify the GroupList query in Design View to identify academic and recreational student groups. Add the GroupTypeID field from the GroupTypes table so it appears immediately before the GroupType field in the query design grid. Move the GroupName field so it appears as the third field in the query design grid. Add criteria to select all records that have a GroupTypeID field value of 1 or 4. Add an ascending sort order to the GroupType and GroupName fields. Save the GroupList query, display it in Datasheet View as shown in Figure 3, and then close it.
Figure 3: GroupList Query in Datasheet View
8. Create a query in Design View using the Departments, Advisors, and Groups tables to list the department, faculty advisor, and group information for each student group. Add the DeptName field from the Departments table, AdvisorFirst and AdvisorLast fields from the Advisors table, and the GroupName, Activities, and Office fields from the Groups table, in that order, to the query. Add criteria to select all records that have an Activities field value of Field and an Office field value of Public. Save the query as ActivitiesAndOffices, display it in Datasheet View as shown in Figure 4, and then close it.
Figure 4: ActivitiesAndOffices Query in Datasheet View
9. Create a query in Design View using the Departments and Advisors tables to calculate a proposed budget increase of 5 percent. Add the DeptName field from the Departments table and the AdvisorLast and Budget fields from the Advisors table, in that order, to the query. Create a calculated field named Proposed that includes the following expression: Proposed: ([Budget]*0.05)+[Budget] Format the Proposed field using the Currency Format property and 2 decimal places. Save the query as ProposedBudget, display it in Datasheet View as shown in Figure 5, and then close it.
Figure 5: ProposedBudget Query in Datasheet 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.