Queries |
Queries allow you to locate specific records in your database by specifying rules for the records you want to view. For instance, if you have a student grades database, you can query the database by asking Access to identify all records in which students grades are greater than a 80%.
1. Create a simple Select Query using the Query Wizard.
To create a query, go to the database window. If Access is already open, the database window is probably open so there should be a button on your start bar that you can click on to activate the window. If the database window is closed, then go to File->Open and locate your database to open it.
Click on the Queries button:
When this button is depressed, the database window will display all previous queries you designed and saved as well as allow you to design a new query either using the wizard or manually. Later in the tutorial we will create a query from Design view. For details on creating more complex queries, use Microsoft Access Help under the Help menu.
Double click on the icon next to the text "Create query by using a wizard." This will open the query wizard window. Select the table from the drop down menu that contains the data set you wish to query:
Then in the Available Fields text box, select the fields that contain the data you wish to query by clicking on the field name to highlight and press the 
. If you wish to select all the available fields then click the 
. If you mistakenly select a field you do not wish to use in your query, highlight the field in the Selected Fields text box and click the 
. Note this button is only available when there are selected fields. For this example I will select studentID and HW1 in order to find out which students had a grade of 8 or higher on HW1. After you have selected the desired fields, click Next.
On this page of the Query Wizard, ensure the "Detail" radio button is selected and click Next. In this window, click the "Modify Query Design" radio button and name your query. I am naming my query HW1Pass. Click Finish. The Query now opens in Query Design View. Similar to how tables have both design and data sheet views, so do queries.
Because I wish to view only the records where students received a grade of 8 points or higher on HW1, I need to add criteria that tells Access to select and display only these records. In the row labeled "Criteria" and under the column labeled "HW1," I add the characters >=8 as shown in the picture below.
To run the query, click the exclamation button, 
. This will take you to the datasheet view where you can see the records that match your criteria. Save the query.
Important Note! While in the Query Datasheet View be careful about changing any data as it will modify the data from the underlying table.
2. Creating a query using multiple criteria.
Now say I want to identify all students who have gotten 8 points or higher on all homework grades.
To alter the query, click on the Query Design View button: 
. Clicking in the next empty cell to the right of the cell containing "HW1" makes a drop down menu appear. This menu contains other possible fields you can use in your query. Because I want to identify students receiving a grade of 8 or higher on all homework assignments, I will create a column for each assignment containing the criteria ">=8," as shown below.
To save this as a different query than the HW1Pass query, go to File->Save As and save it under a different name.
3. Creating a query using expressions.
In sections 1 and 2, the queries simply used criteria to select various records. It is also possible though to make calculations, such as average each student's grade for the homeworks. We will do this by using an expression. Note that this will be a little different because we are operating across each record as opposed to calculating down each field.
This time we will create a query from scratch as opposed to using the wizard. Close out of the current query. Be careful to click on the correct button so you do not close out of the database entirely: 
.
The main database window should now be open. If the Queries button is not already depressed, do so now to display the list of available queries. Double click on the icon next to the text, "Create a query in design view" to open a new blank query. If you have multiple tables in your database a window will open asking you to select which table you would like to use for your query. In this case, I will select the "grades" table.
Clicking in the first cell of the first row labeled "Field" produces a drop down menu. Clicking on the down arrow then allows you to select a field to use in your query. This query will calculate the average of the homeworks for each student so I select StudentID. Click in the next open field cell. This is where we will enter in the expression using the expression builder. To open the expression builder dialog window, click on the "Build" button, 
.
The subsequent window that opens allows you to create a mathematical expression from other values contained in your database. Because I want to compute the average of the homework grades for each student, Access needs to know which fields to pull these values from. Double clicking on the "Tables" folder displays all the tables contained in the current database and thus I click on the "grades" folder which then displays the fields contained within that table, as shown.
To build the expression that will find the average, I double click on HW1 to add it to the text window, type a plus sign, double click on HW2 and type another plus sign, etc until adding all HW fields. I then add in parentheses and divide by 4 to complete the expression:
.
Note how Access adds in the name of the table as part of the expression so it can identify which table to pull the data.
Click on "OK" to close the window and return to the Query Design View.
Click in the cell under your new expression In the row labeled "Total" and then click on the subsequent drop down arrow. Choose "Group By":
Run the query by clicking the run button, .
Congratulations! You have now completed this tutorial.
A little more information about databases... Access contains many powerful functions to contain and manipulate a great deal of data, very few which were touched on in this tutorial. For example, Access also allows you to create forms that allow an interface for inputting data and reports which allow you to format the output of your data. There are many other types of queries such as append queries that allow you to bring in new data from other sources and attach that data to various tables and update queries that allow you to systematically change data already included in your tables. Queries are not limited to single tables either.
Usually, databases contain multiple tables of related information. For example, you could have a table containing personal information about students such as their names and contact information, another table containing their homework grades and yet another table containing their test grades. These tables are connected through their primary keys which you created earlier in the tutorial. This type of database is called a relational database because it relates the various tables together. In the creation of a relational database, it is important that no tables contain duplicate information and no cells should be empty. Database programmers call this process normalizing the tables of the database and there are well defined rules to be followed for tables to be considered normal. If you would like more information regarding databases, their design, and the power of relational databases, then you may wish to explore the Microsoft Access website or the Microsoft Developers Network site on Normalizing Tables.
Back to Index
Back to Tables