Access Practice 3

Prefer to watch and learn? Check out this video tutorial:

Complete the following Practice Activity and submit your completed project.

For Access Practice 3, we will continue to work with the same database we started in Practice 2 that manages student and faculty data in the Honors program at Paradise Valley Community college. For this practice, we will focus on creating queries for the database. We will create queries that answer the following questions:

• • How many students are enrolled in the honors program?
  • How many students have declared Health Sciences as their Field of Interest and how many are undeclared?
  • What students have been awarded a scholarship?
  • What faculty teach in the Honors program, and what students are they assigned to?

1. Open Access, under open, select the database Lastname_Firstname_Access_Practice2. You may need to browse to your flash drive or other location that you saved this database to. In backstage view, select Save As, and save the database as Lastname_Firstname_Access_Practice3. Ensure the database is saved on your flash drive or another safe location.
2. If necessary, select Enable Content to show all of the database content.
3. Open the Navigation pane, and verify there are two tables: Students and Faculty with 38 records each.
4. Close all open database objects. On the Create tab, in the queries group, select Query Wizard. We are going to use the Query Wizard to write a query that will answer the question: How many students are enrolled in the honors program?
5. In the New Query Dialog Box, select Simple Query Wizard and then OK.
6. In the Simple Query Wizard Dialog Box, under Tables/Queries ensure the Table: Students displays.
7. Under Available Fields: Select the following fields. You can double click the field name to add it to the Selected Fields, or use the >.
   • StudentID
   • Last Name
   • First Name
8. Select Next. Title the Query Honors Student List, and select the option to Open the query to view information. Select Finish.
9. Notice how the query opened in Datasheet View. At the bottom of the page, view the record count, and take note that there are 38 records. Practice using the arrows to scroll through the records.
10. Next to Last Name, select the arrow, and select sort A to Z. This will sort the query by Last Name in Ascending order.
11. On the Home Tab, in the Views Group, select Design View. Notice how the Students table displays in the top portion of the window, and the Fields display in the lower portion.
12. Save and close the Honors Student List query. Notice how in the navigation pane, under Queries, the Honors Student List query displays.
13. On the Create tab, in the Queries group, select Query Design. In the Show Table Dialog Box, on the Tables Tab, select Students and then Add. If necessary, close the Dialog Box.
14. We will use the Students table to write a query that answers the questions: How many students have declared Health Sciences as their Field of Interest and how many are undeclared? If necessary, resize the Students table so that you can see all of the fields in the table. You can resize it by selecting the outer edge and dragging.
15. In the Students table view, double click StudentID to add it to the query grid. You can also drag and drop the StudentID field from the Students table into the query grid to add it.
16. Add the following fields to the query grid:
    • Last Name
    • First Name
    • Field of Interest
17. In the query grid, under Field of Interest, in the Criteria row, we will add criteria to determine which students have declared Health Sciences as their Field of Interest. Type Health Sciences in the criteria row under Field of Interest.
18. Notice the Check Marks in the Show row. This indicates whether the field will display when the query is in datasheet view. On the Query Tools Design Tab, in the Results group, select Run. Notice there are 22 students that have declared Health Sciences as their Field of Interest.
19. Right click on Query1 and select Save. In the Save As Dialog Box, save the Query Name as Health Sciences Students and then select Ok.
20. On the Home Tab, in the Views group, change the View to Design View. We will modify this query so that it answers the question: How many are undeclared?
21. In the Query Grid, under Field of Interest, remove the criteria for Health Sciences and replace it with Is Null. This will tell us how many records have a blank, or missing Field of Interest.
22. Run the query. Notice that 7 students are missing a value for Field of Interest.
23. Select the file tab, Save As, then select Save Object As. Then select Save As. In the Save As Dialog Box, change the name of the Query to Students Missing Field of Interest. Ensure it is saved as a query, and then select OK.
24. In the navigation bar, notice how the query we just created displays. With the query still open, change the view to design view. In the query grid, under Field of Interest, in the Show row, remove the checkbox. Save and run the query. Notice how the Field of Interest field no longer displays. Close the query.
25. Using the Query Design, we will create a new query that answers the question: What students have been awarded a scholarship? Add the Students table to the Query Design View. Add the following fields to the query grid:
    • Last Name
    • First Name
    • Scholarship
26. Ensure all three fields are Shown. Under Last Name, sort ascending. To sort the Last Name Field in ascending order, ensure the query is in design view, under the Last Name field, the sort should be set to ascending.
27. Under Scholarship, in the Criteria row, enter Yes.
28. Run the query. Notice there are 20 records, or 20 students that have been awarded a scholarship.
29. Save and name the query Scholarships Awarded.
30. Close the Scholarships awarded query.
31. On the Create tab, in the Queries group, select Query Design. Add the Faculty and Students tables. Notice how the tables are automatically joined. This is because the relationship has already been established. If the tables are not joined, re-visit Practice 2, step 28.
32. We will create a query that answers the question: What faculty teach in the Honors program, and what students are they assigned to? From the Faculty table, add the following fields to the query grid:
    • Faculty ID
    • Last Name
    • First Name
33. From the Students table, add the following fields:
    • Student ID
    • Last Name
    • First Name
34. Save the query as Honors Program Faculty and Students. Run the query. Close the query.
35. Take a moment to verify you have the following tables and Queries in your database.
    • Tables:
      • Faculty
      • Students
    • Queries:
      • Health Sciences Students
      • Honors Program Faculty and Students
      • Honors Student List
      • Scholarships Awarded
      • Students Missing Field of Interest
36. To exit out of the database, first close any database objects that are open. Select the File tab, and take note of where your database is saved. Then select close. Then, exit out of Access.
37. Submit your entire database for grading per your instructor’s instructions.