Access – Chapter 2: Querying a Database
A database query is a question presented in a way that a database can process and respond to it:
“Which clients are located in Berridge?”
“Which recruiters are located in a postal code that begins with ‘804’?”
“How many clients have a balance greater than $500.00?”
Once a query is formed, it can be saved and used repeatedly. When a query is saved, it is the question that is saved. If the query is run again, the answers will be gathered from the database at the time it is run. So, the answers may be different today than they were yesterday.
To create a report from any saved query, use the Report Wizard in exactly the same way as you do for a report from a table.
It is also possible to form a query in such a way that the user can specify a value. For instance, the query might be
“How many clients have a balance greater than ‘x’?”
The user would be able to specify the value they wanted for x.
Creating a query
When you create a query, you specify what fields will be part of the answer, just as you do for a report.
You also specify what records will be part of the answer, by setting up certain criteria. For instance, if you want to know which clients are located in Berridge, you only want the records where the city is equal to Berridge. In this case, Berridge is the criteria.
Using a Criteria in a Query
To create a query to find the Client Number, Client Name, and City of all Clients from the city of Berridge:
1. In the Create tab, click Query Design (at the far right)
2. A work area will open up, and a “Show Table” dialog box will appear. Since all the fields we want are from the Client table, click the Client table, click the Add button, and click the Close button. (Note: if the “Show Table” dialog box doesn’t appear, click Show Table in the Design tab on the Ribbon)
3. All the fields of the Client table are displayed in the top pane, and a set of labeled rows appears in the bottom pane. You can drag the corners of the field box, or the edges of the panes to make more or less work room if you want.
4. Double-click the desired fields in the field box. In this query we wand the Client Number, the Client Name, and the City. Double-click each of these, and they will appear in the lower pane. (Note: if you mistakenly choose the wrong field, right-click on its column in the lower pane and choose Cut)
5. To specify that you only want the clients from Berridge
- Click in the City column, in the row labeled Criteria
- Type in the word Berridge. This indicates that the records that have a City that matches the word Berridge should be chosen.
6. Drop down the View button (at the left side of the ribbon) and choose Datasheet View. The Client Number, Client Name, and City of the Clients from Berridge appear.
7. Choose Design View from the View button.
8. Click the Save icon in the Quick Access toolbar, and when asked for a name, call the query Clients from Berridge
9. Close the query work area
Using wild cards in a criteria
You might not want to totally specify the criteria. For instance, you might want to find all the clients whose names begin with F. You can use wild cards to do so. There are two possible wildcards
· ? is used in place of a single letter. For instance, T?m would match Tim, or Tom, or Tsm.
· * is used in place of any number of consecutive letters, including zero letters. For instance, T* would match Tia or T or Thomas or Te or Thanksgiving
To create a query to find the Client Name, Current Due, and Recruiter Number of all Clients when the Client Name begins with F:
1. In the Create tab, click Query Design (at the far right)
2. In the “Show Table” dialog box, click the Client table, click the Add button, and click the Close button.
3. Double-click the Client Name, Current Due, and Recruiter Number fields to make them appear in the lower pane.
4. To specify that you only want the clients with names beginning with F
- Click in the Client Name column, in the row labeled Criteria
- Type in F*. If you use the tab or arrow key to move the cursor off the field you will see that Access automatically changes this to Like “F*” which is the format it uses internally.
5. Click the Datasheet View button to view the results.
6. Click the Design View button, and save the query as Clients Named F*.
7. Close the query work area
Creating a parameter query
It is useful to set up a query where the user can enter the desired parameter (desired value). For instance, if you set up a query to display the records of all the clients of recruiter number X, the user could specify which recruiter number he/she wanted to see:
1. In the Create tab, click Query Design (at the far right)
2. In the “Show Table” dialog box, click the Client table, click the Add button, and click the Close button.
3. Double-click the Client Name, Street, City, State, Postal Code, and Recruiter Number fields to make them appear in the lower pane.
4. To specify that you only want the clients with a recruiter number specified by the user
- Click in the Recruiter Number column, in the row labeled Criteria
- Type an opening square bracket, a prompt to the user, and a closing square bracket. For example [Enter Recruiter Number]
- Note: it is very important that the prompt is NOT the same as the name of a field
5. Click the Datasheet View button. Instead of displaying results, Access displays a dialog box with the prompt you typed in.
6. Enter a recruiter number in the dialog box (either 21, 24, or 27)
7. The appropriate results will be displayed.
8. Click the Design View button, and save the query as Clients of a Recruiter.
9. Close the query work area
10. In the Navigation pane, double-click the query called Clients of a Recruiter
11. When prompted, enter a recruiter number.
12. After the results are displayed, close the results.
Using a number in a criterion
If you want results that are exactly equal to a number, follow the same process as when matching a text criterion.
However, often you want to find numeric values that are greater than or less than a certain value.
To find the names of recruiters who have commissions greater than or equal to $10,000:
1. Began Query Design, add the Recruiter Table, and select the Last Name, First Name, and Commission fields
2. To specify that you only want the recruiters with a commission greater than or equal to $10,000
- Click in the Commission column, in the row labeled Criteria
- Type >=10000. Don’t use a dollar sign or comma; you will get an error.
3. Click the Datasheet View button to display the results.
4. Save the query with an appropriate name
5. Close the work area
Note: the comparison operators you can use are >, <, >=, <=, and NOT.
Editing a query
To use the Clients from Berridge query to make a new query that shows all the clients who are NOT from Berridge:
1. Right-click on the Clients from Berridge query.
2. Choose copy
3. Right-click in any part of the Navigation pane, and choose paste
4. A dialog box will appear asking for a name for the copy. Enter Clients Not from Berridge
5. The copy will be added in the Clients section. Right-click it, and choose Design View
6. The work area opens, with the query in place. In the city criteria, before the opening quotation mark type Not and a space, so the final entry is Not “Berridge”
7. Click the Datasheet View button to view the results.
Sorting
8. Still working with the same query, click the Design View button.
9. In the City column, click the Sort row. Using the drop-down arrow, click Ascending to sort the results in ascending order by the name of the city.
10. Click the Datasheet View button to view the results.
11. Click the Design View button.
12. Save the query
Not displaying a sort or criterion field
13. Still working with the same query, uncheck the Show Results box in the City column
14. Click the Datasheet View button to view the results. The records are still sorted by city name, and the only records showing are where the city is not Berridge, but the city field does not show.
15. Close the work space. When asked if you want to save the changes, say no.
More than one table in a query
In many cases, you will need fields from more than one table to answer the question posed by your query. For instance, “What is the name of the recruiter handling the Ferb Dentistry account?” Although the Client table has the number of the recruiter, that number needs to be matched up with the recruiter’s name from the Recruiter table to answer the question.
To find the name of the recruiter handling the Ferb Dentistry account:
1. Began Query Design
2. Because the results will come from both the Client table and the Recruiter table, in the “Show Table” dialog box, add the Recruiter Table then add the Client Table, and click Close
3. Note that the tables have a joining line between the Recruiter Number in each table. This is the field that both tables have in common, and they are joined by this field.
4. From the Client table, select the Client Name and the Recruiter Number fields.
5. From the Recruiter table, select the First Name and the Last Name fields. You don’t have to select the Recruiter Number from both tables, only one is needed.
6. If you click Datasheet View now, you will see the name of the recruiter for every client. However, we only want the Ferb Dentistry record, so in Design View type Ferb Dentistry in the criteria row of the Client Name field. Make sure you spell it correctly.
7. Click the Datasheet View button to display the results.
8. Save the query with an appropriate name
9. Close the work area
Calculations in a Query
There are two types of calculations that can be done in a query. A Calculated Field is a calculation that is done on individual records, such as adding the values in two fields together. A Group Calculation involves groups of records, such as finding the average or the total of the group.
Calculated Field: For each client, find the Total Amount by adding together the Amount Paid and the Current Due. Display the Client Number, Client Name, Amount Paid, Current Due, and Total Amount.
1. Began Query Design, add the Client Table, and select the Client Number, Client Name, Amount Paid, Current Due fields
2. To add the Total Amount field
- In the next column after Current Due, type the name of the new field, a colon, and the mathematical expression you want calculated. Any field names that are part of the expression must be enclosed in square brackets. For this query, enter Total Amount:[Amount Paid]+[Current Due]
- Note: Make sure the name of this new calculated field is different from any other field in the table
3. Click the Datasheet View button to display the results.
4. Save the query with an appropriate name
5. Close the work area
Group Calculation: “Calculate the total Amount Paid for all clients.”
1. Began Query Design, add the Client Table. Select the Amount Paid field.
2. Click the Totals button on the Design tab. A new row called Total will be added to the lower pane.
3. Click in the Total cell of the Amount Paid column to display the drop down arrow. Select Sum
4. Click the Datasheet View button to display the results. Note that there is only one amount, because this is a total for all records.
5. Save the query with an appropriate name
To show the sum of a group: “What is the total amount paid by the clients of each recruiter?”
6. Return to Design View
7. Add the Recruiter Number field to the lower pane
8. Make sure the Total drop down box for Recruiter Number has Group By selected. This will group the results by recruiter numbers.
9. Click the Datasheet View button to view the results.
10. Close the work area without saving the query.
C:\Classes\11000Intro\Chapters\ExcelChapter2b.docx