Open the Database window and click on Queries under Objects.
Double click on Create query in Design view.
|
|
Access is capable of performing calculations like Excel.
However, Access only supports calculations in queries.
Open the Database window and click on Queries under Objects. Double click on Create query in Design view. |
|
Two windows will pop up the Show Table and the Select Query window
The Show Table window allows you to select the tables that you will use.
Highlight student and click on Add. If you have more tables to be inserted, select those tables and click on Add. When you’re done, click on Close. We'll keep it simple and stick to one table per database. |
|
|
Double
click on the field names in the small student menu to show them in the Query.
In the student table window double click on Name, Class Name, Student #, and Test Mark for now. Now move to the next available
empty column and click on the build
|
| The Expression Builder allows you to calculate a value that will appear in the query results. The top half shows the formula, the bottom half lets you select which fields to insert into the formula. In this example, double click on Tables and then click on student. Now in the middle bottom space you will see all the fields that are in the student table. Double click on Test Mark field, then select the / operator, then double click on the Max Score field, then the * operator and finally type in 100. Now click OK. |
You should be seeing the following:
|
|
The column where you put in the formula will look like the image on the left. Scroll back to the very beginning to change Expr1: to Test Grade:. Do not forget the colon after Test Grade or else the expression will not work. |
|
Click on the Run icon,
to build this Query.
|
You should end up with something like this:
To format the calculated fields to have only a certain number of decimal places, open your query in design view. Then select the field you wish to format by clicking on it. Now click the Properties icon in top tool bar.
| You should have opened the Field Properties window. Within this window change Format to "Fixed" by clicking on the Format option and using the drop down menu. Now switch the Decimal Places by clicking on the Decimal Places option and using the drop down menu. |
|
Exercise:
A Query enables you to find records that match multiple conditions. With sophisticated queries, you can specify conditions to find text, numbers, or dates that fall within certain ranges.
To specify a query, view your query, go to the Records menu, select Filter, and then Advanced Filter/Sort. Now select the fields Name, Student #, Class Name, and Test Grade like you did before for mathematical formulas.
To specify a Query you need to let Access know what you are interested in querying. Say you want to see all the students who have a test grade over 75%, you would enter ">=75" in the Criteria cell under Test Grade
Click on the Apply Filter icon 
to apply the Query.
You will see all the students that have 75 or greater for a Test Grade.
To see how many records are in your query, at the bottom left of the query window there is a count. It looks like this:
To again view all of the records in the table, click on
the icon to
remove the filter.
Note: if no records fit the filter criteria, an appropriate error message will appear.
Exercise:
Go to the Records menu, select Filters, and select Advanced Filter/Sort
Enter a query for "Name" field being "equal to" "Insert a Name in your database here" for instance "Abbie Good".
You should see just that one record displayed. The rest of your database is still there, but it is just not shown.
Remove the filter.
For example, say you want to see all the students who passed cs 100 you would set up the query as follows:
When you apply the query, you will see the CS100 students who have passed. Only those records matching the two criteria, that is, a CS100 student, and a student who passed, are displayed.
To create an AND or an OR statement start a new filter by following the same steps as above. Underneath the Criteria cell is an Or: cell, use this cell to create an OR statement. To create an AND statement type AND between the entries, like this:
An OR statement is created like this:
Exercise:
Go to Records, Filters, and Advanced Filter/Sort to do the following:
Implement a query for:
CLASS NAME is equal to cs 100
AND
TEST MARK is greater than 80
Go to the Tools menu and select Filters.
Implement a query for:
CLASS NAME is equal to cs 100
OR CLASS NAME is equal to bio 100
icon in the main toolbar. The
build icon starts the Expression Builder that you will use when you want to add
a calculation to a query.