Week 10: Queries Continued, Testing Tables... Yes Again!
Ranges in Criteria
Instead of using specific criteria, you can specify that you want the results to find all records within a certain range.
Using an Upper or Lower Limit
Logical Operators can be used to set a logical limit on the range or results. If you want to specify a “Less than” criteria you can use the < symbol. To specify a “Greater than” criteria, use the > symbol.
7) Clear any existing criteria from your query.
8) Add the field DOB to the Query
9) For the DOB criteria, enter <1/7/98.
7) Clear any existing criteria from your query.
8) Add the field DOB to the Query
9) For the DOB criteria, enter <1/7/98.
Notice that the criteria places the # symbol around the date
10) Click the Datasheet View icon to view the results of query. You will see all students with birth dates before 1/7/98. Return to the Design view.
11) Delete the criteria in the Date of Birth field.
12) Scroll right to the Mark field and enter >70 for the criteria.
11) Delete the criteria in the Date of Birth field.
12) Scroll right to the Mark field and enter >70 for the criteria.
13) Click the Datasheet View icon to view the results of query. You will see all students with a mark higher than 70. Return to the Design view.
14) Delete the criteria in the Mark field.
15) Scroll left to the Surname field and enter <M for the criteria.
14) Delete the criteria in the Mark field.
15) Scroll left to the Surname field and enter <M for the criteria.
16) Click the Datasheet View icon to view the results of query. You will see all students a last name staring with a letter that comes before M in the alphabet. Return to the Design view.
You can see from these examples that range criterion work equally well with numbers, dates and text.
17) Try the following criteria in your query. Remember to clear each one before trying the next one.
You can see from these examples that range criterion work equally well with numbers, dates and text.
17) Try the following criteria in your query. Remember to clear each one before trying the next one.
Parameter Queries
In the last section you created queries with various criteria. If you wanted to keep a query with specific criteria you could save it.
For example, if you wanted a query that shows all students who live in Blackburn, you could create a query with appropriate criteria and save it with a name such as students in blackburn.
It would get a little complicated, however, if you wanted a query for every city, especially if your database contained a dozen different cities. Parameter queries are a solution to this problem.
A parameter query allows you to create a query that prompts the user for the criteria when the query is run. You could set up a query so that when it is run, it asks the user to enter a city and uses whatever they enter as the criteria. You could use the same query repeatedly without having to modify the design if you want to change the criteria.
Creating a parameter query is like creating a regular select query. The only difference is that where you would normally enter your query criteria, you instead enter square brackets [ ]. This tells access that the user will be prompted for the criteria in this field. If you want to customise the message that is used to prompt the user you can type your own custom message between the square brackets.
For example, if you wanted a query that shows all students who live in Blackburn, you could create a query with appropriate criteria and save it with a name such as students in blackburn.
It would get a little complicated, however, if you wanted a query for every city, especially if your database contained a dozen different cities. Parameter queries are a solution to this problem.
A parameter query allows you to create a query that prompts the user for the criteria when the query is run. You could set up a query so that when it is run, it asks the user to enter a city and uses whatever they enter as the criteria. You could use the same query repeatedly without having to modify the design if you want to change the criteria.
Creating a parameter query is like creating a regular select query. The only difference is that where you would normally enter your query criteria, you instead enter square brackets [ ]. This tells access that the user will be prompted for the criteria in this field. If you want to customise the message that is used to prompt the user you can type your own custom message between the square brackets.
Task 4:
Creating a Parameter Query
1) Make sure your SchoolPractice database is open and make sure you are in the Query Design view.
2) Double-click, STUDENTS in the Show Table dialog to add it to the query design and then click Close to move to the design window.
3) Add the following fields to the QBE grid:
• Surname
• First Name
• Street Address
• City
• Postcode
• State
4) In the criteria row for the City field, add two square brackets as shown.
2) Double-click, STUDENTS in the Show Table dialog to add it to the query design and then click Close to move to the design window.
3) Add the following fields to the QBE grid:
• Surname
• First Name
• Street Address
• City
• Postcode
• State
4) In the criteria row for the City field, add two square brackets as shown.
5) Click the View icon to view the results of the query. A prompt will appear asking you to enter the criteria for the field.
6) Enter Geelong and click OK. The query results will show students from Geelong.
7) Click the View icon to return to design view.
We will put a custom message in the criteria prompt. Enter a custom message between the square brackets so that it appears as the following. [Enter a city]
We will put a custom message in the criteria prompt. Enter a custom message between the square brackets so that it appears as the following. [Enter a city]
10) Click the View icon again. This time the prompt will include your custom message.
11) Enter Melbourne and click OK (or press [Enter]. This time you will see all students from Melbourne.
12) Return to design view.
13) Click the Save icon. Name the query parameter: students by city.
14) Close the query when done.
Note If you try running a parameter query and get a message about Replication IDs, it is probably because you uses curly brackets { } instead of square brackets [ ].
11) Enter Melbourne and click OK (or press [Enter]. This time you will see all students from Melbourne.
12) Return to design view.
13) Click the Save icon. Name the query parameter: students by city.
14) Close the query when done.
Note If you try running a parameter query and get a message about Replication IDs, it is probably because you uses curly brackets { } instead of square brackets [ ].
Using a Saved Parameter Query
Your parameter query should now appear in the query list.
1) Double-click the parameter: students by city query to run it.
1) Double-click the parameter: students by city query to run it.
2) Enter Geelong as the City and click OK. You will see all of the students who live in Geelong.
3) Close the query. You should be returned to the Database Window.
4) Double-click the parameter: students by city query to run it again.
5) Enter Melbourne as the suburb and click OK. You will see all of the students who live in Melbourne.
You can run the query with any city being used as the criteria without changing the design of the query
3) Close the query. You should be returned to the Database Window.
4) Double-click the parameter: students by city query to run it again.
5) Enter Melbourne as the suburb and click OK. You will see all of the students who live in Melbourne.
You can run the query with any city being used as the criteria without changing the design of the query
Creating a Parameter Query with Multiple Criteria
In regular select queries, you can have criteria on as many fields as you like. This applies for Parameter queries as well. If you have more than one parameter criteria in a query then a prompt will appear for each criterion, one after the other before the query results appear. You can also use parameters for range criteria as the following exercise demonstrates. We will create a query that allows the user to enter two amounts, and see all students with marks between those amounts.
1) Create a new query that uses the following fields of the Student table:
• Surname
• First Name
• Gender
• Mark
2) For the Mark field, enter the following for the criteria.
>=[Enter a minimum mark] and <=[Enter a maximum mark]
Tip When you are working with long criteria like this, it can be difficult to edit because of the limited space. To make it easier, right-click on the criteria and click Zoom. You will then have a nice large window to edit your criteria. When you are done you can click OK to confirm the changes.
3) Click the View or Run icon to view the results of the query. A prompt will appear asking you to enter a minimum mark.
4) Enter 50 and click OK. A second prompt will appear asking you to enter a maximum mark.
5) Enter 70 and clock OK. The results will show all students with marks from 50 to 70.
6) Click the View icon to return to design view.
7) Save the query as parameter: students marks between two amounts.
8) Close the query when it is saved.
Using Wildcards with Parameters
1) Open the parameter: students by city query.
2) When the parameter prompt appears, enter Gee* for the criteria
2) When the parameter prompt appears, enter Gee* for the criteria
If this criterion were used in a query design, it would result in all students where the city name begins with Gee. This won’t work with parameters though and you will get no results at all. For parameters, the wildcard needs to be built in to the query design along with the parameter.
3) Close the query to return to the Database Window.
4) Create a new query using the following fields.
• Surname
• First Name
• Date of Birth
• Phone
• Gender
• Mark
5) For the Surname field, enter the following for the criteria.
Like [Enter the surname] & *
4) Create a new query using the following fields.
• Surname
• First Name
• Date of Birth
• Phone
• Gender
• Mark
5) For the Surname field, enter the following for the criteria.
Like [Enter the surname] & *
The & symbol will join the user’s typed criteria on to a * wildcard.
This will mean that the user will only need to enter the first few letters of a last name to get the results.
6) Click the View icon to view the results of the query.
7) When the prompt appears enter S for the criteria and click OK. You will see all students with a last name beginning with the letter S. This makes the query easier for the user.
This will mean that the user will only need to enter the first few letters of a last name to get the results.
6) Click the View icon to view the results of the query.
7) When the prompt appears enter S for the criteria and click OK. You will see all students with a last name beginning with the letter S. This makes the query easier for the user.
8) Click the View icon to return to design view.
9) Save the query as parameter: students by surname.
10) Close the query when it is saved.
Some variations on the above method are shown below.
9) Save the query as parameter: students by surname.
10) Close the query when it is saved.
Some variations on the above method are shown below.
11) Create queries to test each of the two examples shown.
12 ) Create 2 more queries for your RDBMS
Save and submit your whole file under Task 4.
12 ) Create 2 more queries for your RDBMS
Save and submit your whole file under Task 4.
Calculated Fields
Queries can include additional fields which automatically calculate a result based on existing fields. The calculations in these fields are similar to the calculations in Excel formulae.
In the following exercise we will create a basic calculated field to work out a student’s age from their Date of Birth.
The following guidelines apply to calculated fields.
2. When you are referring to a field in your calculation, the field name must be enclosed in square brackets. E.g. [Mark].
3. You can’t combine different data types in a calculation. E.g. You can’t add a number field to a text field.
In the following exercise we will create a basic calculated field to work out a student’s age from their Date of Birth.
The following guidelines apply to calculated fields.
- Like Excel, calculations in access follow the order of operations rules:
- Brackets calculated first
- Indices (^) calculated next E.g. 4^2 means 4 to the power of 2
- Multiplication and division calculated next
- Addition and subtraction calculated last.
2. When you are referring to a field in your calculation, the field name must be enclosed in square brackets. E.g. [Mark].
3. You can’t combine different data types in a calculation. E.g. You can’t add a number field to a text field.
Task 5:
Creating a Calculated Field
1) Create a new query with the following fields:
• Surname
• First Name
• Date of Birth
2) Click in the blank field after Date of Birth as shown.
The calculation is typed where the field name would normally go.
In an Excel calculation, you would begin with an equal sign. In a calculated field, you begin with a name for the calculated field followed by a colon.
This calculated field is to calculate a student’s age.
3) Enter the following in the Field row (you may like to use the Zoom option shown previously when you are editing the calculation).
Age: (Date()-[DOB])/365
• Surname
• First Name
• Date of Birth
2) Click in the blank field after Date of Birth as shown.
The calculation is typed where the field name would normally go.
In an Excel calculation, you would begin with an equal sign. In a calculated field, you begin with a name for the calculated field followed by a colon.
This calculated field is to calculate a student’s age.
3) Enter the following in the Field row (you may like to use the Zoom option shown previously when you are editing the calculation).
Age: (Date()-[DOB])/365
4) This calculation will subtract their date of birth from the current date date() to calculate their age in days. The result is then divided by 365 to get the age in years.
5) Click the View icon to view the results of the query.
The calculation will appear as an additional field, with the result appearing for each student.
5) Click the View icon to view the results of the query.
The calculation will appear as an additional field, with the result appearing for each student.
6) Click the View icon to return to design view.
7) Save the query as student listing with age.
7) Save the query as student listing with age.
Formatting a Calculated Field
In the previous exercise, the resulting calculated field contained ages with a large number of decimal places. You can customise fields in a query by editing properties that a very similar to the ones found in table design.
1) Make sure you still have your student listing with age query open in design view.
2) Right-click on the Age field and choose Properties (you can also get to properties from the View menu, by clicking the properties icon or by pressing [Alt][Enter]).
1) Make sure you still have your student listing with age query open in design view.
2) Right-click on the Age field and choose Properties (you can also get to properties from the View menu, by clicking the properties icon or by pressing [Alt][Enter]).
The Properties window will show properties for the age calculated field (you can also edit properties for any regular field in the same way).
3) Click in the Format property and change the format to Fixed.
4) Click in the Decimal Places property and enter 1.
3) Click in the Format property and change the format to Fixed.
4) Click in the Decimal Places property and enter 1.
5) Close the Properties window.
6) View the results of the query again. The ages will now be formatted with one decimal place.
6) View the results of the query again. The ages will now be formatted with one decimal place.
7) Save and close the query.
Using a Function in a Query
Functions in Access are similar to functions in Excel and can be used to simplify complex calculations. The following example demonstrates the use of the Immediate If function which is similar to Excel’s If function.
1) Create a new query with the following fields:
• Surname
• First Name
• Mark
2) In the next blank field, enter the following.
Pass: IIf([Mark]>=50,"Pass","Fail")
1) Create a new query with the following fields:
• Surname
• First Name
• Mark
2) In the next blank field, enter the following.
Pass: IIf([Mark]>=50,"Pass","Fail")
The first part will be the name of the new field (Pass).
This is followed by the IIF. Like all functions, its components are enclosed in brackets. This type of function has three parts separated by commas.
The first part specifies the criteria. In this case [Mark]> meaning are the contents of the Mark field greater than or equal to 50.
The second part specifies what the answer will be when the condition is true (display the text “Pass”). When test is being referred to in a calculation it always need to be enclosed in “ ”.
The third part specifies what the answer will be when the condition is false (display the text “fail”).
3) Click the View icon to view the results of the query.
Any student with a mark less than 50 will have Fail in the Pass field.
This is followed by the IIF. Like all functions, its components are enclosed in brackets. This type of function has three parts separated by commas.
The first part specifies the criteria. In this case [Mark]> meaning are the contents of the Mark field greater than or equal to 50.
The second part specifies what the answer will be when the condition is true (display the text “Pass”). When test is being referred to in a calculation it always need to be enclosed in “ ”.
The third part specifies what the answer will be when the condition is false (display the text “fail”).
3) Click the View icon to view the results of the query.
Any student with a mark less than 50 will have Fail in the Pass field.
4) Click on the mark for Alison Jacobs as shown above.
5) Change the mark to 48. As soon as you move on to a different record, the Pass column will update.
6) Make sure you are still in the Mark column and click the Sort Descending icon so that the highest marks are at the top. Changes to the sort order will be saved as part of the query design.
5) Change the mark to 48. As soon as you move on to a different record, the Pass column will update.
6) Make sure you are still in the Mark column and click the Sort Descending icon so that the highest marks are at the top. Changes to the sort order will be saved as part of the query design.
7) Save the query as student marks and close the query when done.
Note When you are viewing the results of a query you are actually viewing a selection from the table. When you change data in a query’s datasheet view those same changes are being made to the table data. Any formula depending on that data will automatically update.
Note When you are viewing the results of a query you are actually viewing a selection from the table. When you change data in a query’s datasheet view those same changes are being made to the table data. Any formula depending on that data will automatically update.
Summary Queries
Unlike other queries you have done, a summary query won’t show individual records. It will only show a summary in the form of averages, totals and other available calculations. Summary Queries are useful for reporting where you are only interested in a summary of the information without the detail.
Creating a Query with a Total
1) Create a new query with only the Mark field.
2) To change to a Summary Query, select Totals from the Design toolbar menu
2) To change to a Summary Query, select Totals from the Design toolbar menu
3) Your QBE grid will now include a Total row.
4) Change the Total figure for the Mark field from Group By to Sum.
5) View the results of the query. Because of this change the query result will now show the Sum of the Mark field instead of showing each record.
6) Return to Design View.
7) Add the Mark field to the QBE grid three more times. Change the total row for each one to Avg, Min and Max.
8) View the results of the query. The results will now show the Sum of the Mark field, the Average of the mark field, the Minimum amount in the Mark field and the Maximum amount in the Mark field.
9) Return to Design View.
10) Save the query as summary: student marks.
7) Add the Mark field to the QBE grid three more times. Change the total row for each one to Avg, Min and Max.
8) View the results of the query. The results will now show the Sum of the Mark field, the Average of the mark field, the Minimum amount in the Mark field and the Maximum amount in the Mark field.
9) Return to Design View.
10) Save the query as summary: student marks.
Using Grouping in a Summary Query
When using Summary Queries it is common to group the results by a particular field instead of viewing the totals for the entire query.
1) Drag the Gender field on to the first Mark field. This will insert the Gender field to the left of the first Mark field.
1) Drag the Gender field on to the first Mark field. This will insert the Gender field to the left of the first Mark field.
2) Leave the Total row for the Gender field as Group By.
3) View the results of the Query. Now you will see totals for the Female students and totals for the Male students.
4) Return to Design View.
5) Add the Suburb field as shown below.
3) View the results of the Query. Now you will see totals for the Female students and totals for the Male students.
4) Return to Design View.
5) Add the Suburb field as shown below.
6) View the results of the query. The results will now show the totals grouped by Gender and grouped by Suburb.
7) Save and close the query.
Submit your file via compass
Submit your file via compass
Task 6: Additional Practice
Open the following file:
games.accdb |
And produce at least 5 criteria.
You must at least use:
You must at least use:
- Like
- &
- Parameter Query []
- Calculated Fields
- *