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?
- 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.
- If necessary, select Enable Content to show all of the database content.
- Open the Navigation pane, and verify there are two tables: Students and Faculty with 38 records each.
- 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?
- In the New Query Dialog Box, select Simple Query Wizard and then OK.
- In the Simple Query Wizard Dialog Box, under Tables/Queries ensure the Table: Students displays.
- 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
- Select Next. Title the Query Honors Student List, and select the option to Open the query to view information. Select Finish.
- 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.
- Next to Last Name, select the arrow, and select sort A to Z. This will sort the query by Last Name in Ascending order.
- 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.
- Save and close the Honors Student List query. Notice how in the navigation pane, under Queries, the Honors Student List query displays.
- 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.
- 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 undec