Access InClass Exercise

www.nvcc.edu/home/jumiller/115/access/hw_access_1.html describes the tables in the database we will use. We will create the tables, enter the first two records in each table, and create the relationship between the tables. Once we see how it is done, we will use a larger version of the School database.for the queries below.

1. Create a query called DC Students, listing the names of all students who live in DC.
2. Create a query called DC or MD Students, listing the last names and the states of all students who live in DC or MD.
3. Create a query called Composition and VA Students, listing the Student ID, First Name, Last Name, CourseName, and State of all Composition students who live in VA.
• The queries based on the Students and Courses table must also show the Student_Courses table, so Access understands what the relationship is between the tables.
4. Create a query called Composition or VA Students, listing the First Name, Last Name, CourseName and State of all students who are either taking Composition or who live in VA
5. Create a query called Composition or World History Students, listing the First Names and Last Name, CourseName and State of all students who are taking either Composition or World History.
6. Create a query called Large IDs listing the StudentID, Last Name, and City of all students with a StudentID larger than 9.
7. Create a query called Average ID, which finds the average of the StudentID numbers.
   • First choose the StudentID field, so it appears at the bottom of the Select Query box.
   • Then click on the Totals button on the Toobar. The totals button looks like the funny capital E.
   • After you click on the Totals button, you will see a new line appear below, in the Select Query box. The new line says "Total".
   • Change the "Total" line from "Group By" to "Average."
8. Create a query called Cities.  List all cities where the students live, in alphabetical order. Make the city names unique (do not repeat any city).
   • Just show the cities field. Do not show any other fields.
   • To sort the cities, click on the "Sort" line below the City field, then select "Ascending"
   • To make the list of cities unique (so that the city names do not show up more than once when you run the query):
     • Click in an open area on the top part of the Design View.
     • Choose "Properties" (in the upper right corner of the design ribbon, next to the Totals button)
     • Change "Unique Values" from "No" to "Yes"