Lab Assignment 3: Building Queries
Objective: Familiarize
students with creating queries to retrieve and analyze data.
Step 1: Open the Existing Database
Open the database
you've been working with in previous lab assignments. If you don't have an
existing database, create one and add a table with sample data.
Step 2: Create a Query to Select
Students from a Specific Department
1. Go to the
"Create" tab in Access.
2. Click on the
"Query Design" button to open the Query Design view.
3. Add the
"Student Information" table to the query design grid.
4. Double-click on the
fields you want to include in the query. For this query, select the fields
"StudentID," "Name," "Email," and
"Department."
5. In the criteria row
of the "Department" field, enter the department you want to filter
by. For example, if you want to select students from the "Computer
Science" department, you would enter "Computer Science" in the
criteria row.
6. Save the query with
a descriptive name, like "Students in Computer Science Department."
7. Run the query to
view the results. It will display a list of students in the specified
department.
Step 3: Create a Query to Count
Students in Each Department
1. Go to the
"Create" tab in Access.
2. Click on the
"Query Design" button to open the Query Design view.
3. Add the
"Student Information" table to the query design grid.
4. Add the
"Department" field to the query grid.
5. In the
"Total" row of the "Department" field, select
"Count" from the drop-down list.
6. Run the query. It
will display the count of students in each department.
Step 4: Create a Query that Combines
Data from Multiple Tables Using Joins
For this step, you
may need to create or have multiple related tables in your database. Let's
assume you have two tables: "Students" and "Courses." You
want to create a query that shows the courses each student is enrolled in.
1. Go to the
"Create" tab in Access.
2. Click on the
"Query Design" button to open the Query Design view.
3. Add the
"Students" and "Courses" tables to the query design grid.
4. Create a
relationship between the "StudentID" field in the
"Students" table and the "StudentID" field in the
"Courses" table by dragging and dropping these fields in the query
design grid.
5. Select the fields
you want to display in the query. For this query, select
"StudentName" from the "Students" table and
"CourseName" from the "Courses" table.
6. Run the query to
view the results. It will display a list of students and the courses they are
enrolled in.
These steps should help your students complete Lab Assignment 3
successfully. Make sure to provide any necessary sample data and be available
to answer questions as they work on their queries.