Introduction to Health Informatics
Access practical session 4 - Selection Queries (1)
Introduction to Access97 - Session 4
Selection Queries (1)
by Robin Beaumont
e-mail:
Contents
1.Learning outcomes check list for the session......
2.Introduction......
3.Creating a query......
4.The Query design Window......
5.Adding fields to the result......
6.Running a query......
7.Saving a query......
7.1Saving a query definition......
7.2Saving the results of a query......
8.Clearing the QBE grid and adding tables......
9.Specifying a query......
10.Specifying criteria for number fields......
10.1A specific value......
10.2A range of values......
11.Specifying criteria for text fields......
11.1A specific value......
11.2A range of values......
11.3Wild cards......
12.Specifying criteria for date fields......
12.1A specific value......
12.2A range of values......
12.3A range of values before or after today......
13.Multiple Criteria......
13.1And......
13.2Or......
14.Blank records - looking for, or excluding from a query......
15.Review exercises......
16.Summary......
17.Check what you have learnt......
18.Answers......
18.1How to implement an exclusive or (xor)......
18.2Answers to selected 'Review exercises'......
This handout is part of a course. For details please see Section 7.1 at:
1.Learning outcomes check list for the session
Each of the practical sessions aims to provide you with a number of skills (the 'be able to's' below) along with the relevant information (the 'know what's' below). After you have completed the session you should come back to these points ticking off those with which you feel happy.
Learning outcome / Tick boxDescribe what an Access query is /
Describe the components of an Access query /
Be able to create a query using the query design window /
Be able to add tables to the query design window /
Be able to run a query /
Be able to save a query definition /
Know how to save the results of a query to a table /
Be able to specify criteria for number fields for one specific value and a range of values /
Be able to specify criteria for text fields /
Be able to explain and use wild cards /
Be able to use the 'And' operator /
Be able to use the 'OR' operator /
Know the difference between 'OR' and 'XOR' /
Be able to find or exclude blank (NULL) fields /
2.Introduction
This session will begin to investigate queries. Queries are a method of examining or manipulating one or more tables to obtain useful information. For example, we might wish to examine the patient table in the consultations database for the patients that live in a particular city or who where registered before 1970. This session will both demonstrate, and get you to carry out, such queries.
A query consists of three components:
- The tables to query
- Query definition
- Result
These can be thought of as the input, process and output. The input is simply one or more fields from one or more tables in a database. The process is interrogating the table, using a special facility called QBE, and the output is the result. A result can be either the creation of a temporary dataset, the create of a results table or some type of updating to the current table.
This session will look specifically at Select queries which result in a new temporary set of records being produced based upon some such criteria defined in the QBE facility.
This session uses the cons2 database which contains the following records in the Doctor table.
Exercise:
Open the cons2 database now.
Consultations database, Doctor table.
Doc id / doc first name / doc Surname / Gender / Date reg / Addrs title / Addrs st name / Addres st no / city / postcodeA / postcodeB / Phone No1 / john / SMITH / 2 / 01/04/67 / The old surgery / 12 station rd / Walkergate / Newcastle / Ne23 / 4rr / 0191 009282
2 / steve / Jarvis / 2 / 01/05/70 / Morpeth HC / High st / Morpeth / Mo2 / 1ts / 0127 485739
3 / Mary / Goodall / 1 / 27/11/67 / Seeham HC / The harbour / Seaham / Se1 / 4jp / 0191 345867
4 / Fiona / black / 1 / 30/01/76 / Cragside HC / The East Wing / Cragside castle / Cr3 / 1ws / 0146 389431
5 / Anna / Scriabin / 0 / 25/03/61 / Uniiverisity MC / Newcastle Univeristy / Newcastle / Ne1 / 1aa / 0191 445776
23 / mark / Goodall / 2 / 27/02/55 / Seaham HC / The harbour / Seaham / Se1 / 4jp / 0191 345867
3.Creating a query
At with most things in Access97 you can create them several different ways. Access97 provides query design Wizards, like the one you used earlier for creating a form, however we will develop the queries in this session using the query design window. Opening this window is left as an exercise for you.
Creating a query Exercise:
In the database window select the query tab then click the New button. The relevant dialogue boxes are pictured below.
From the 'New Query' dialogue box' select the 'Design view' option then click on the 'OK' button.
Query help Exercise:
Call up Access97 office assistant help by pressing the F1 key or the appropriate toobar icon. Read through the following help topics
'Queries what they are and how they work' (read all three pages)
'Create a Query' (just have a quick skim through the material. You are not expected to understand much of it at the present).
'Switch between views of a query'
Then click on the 'design view',
Datasheet view' and 'SQL view'
each of the three views
4.The Query design Window
You will now be presented with the query design window, this is one of the most complex in Access. And what makes it worse, is the fact that it is partially obscured by the Show table dialogue box, asking you which tables you would like to query in the present database.
In the following sections, we will carry out a number of queries on the 'doctor' table. As a preliminary step the table needs to be added to the query design window.
Exercise:
From the 'Show table' dialogue box Choose the doctor table then click the add button.
Click the close button on the dialogue to hide it for now. Note if you need it again you can easily call it up from the menu.
You should now have something that looks like the picture below.
The query window consists of two parts. The top part shows graphically what tables you are using to develop the query. The bottom part is a graphical query tool using a method known as QBE (Query By Example) this is also used in other databases so learning QBE in Access means you can transfer to another database relatively easily.
Rather than describing all the features of the query design window, of which there are many, we will carry out a few very simple examples using firstly the doctor and than the patient tables.
5.Adding fields to the result
The first thing to do is to decide for which fields you want to see the results. This is achieved by dragging the necessary fields from the top part of the window to the grid in the bottom part. You can drag individual fields by selecting each individually or, alternatively choosing the asterisk at the top of the field list and dragging that to choose all the fields. We will use yet another method.
Selecting all the fields form a table table for a query exercise:
The QBE grid should now look similar to the picture below.
Depending upon the way Access97 has been setup on your machine you may have an additional row labelled 'Table:' along with the value 'doctor' in each cell. This additional information is useful if you have a query that uses more than one table. For now it doesn't matter. You can hide or show the row by going to the menu option view -> table names if it worries you.
Inspecting the grid you should note:
- The field names are listed along the top.
- The 'sort' cells are empty. You request various options by clicking on the cells.
- A show button is clicked for each field. You may be wondering why you would need a field in the QBE window that allows you to include fields in the query that you do not intend to show. There are many reasons for this. You may wish to select records based upon certain criteria for a field which you do not wish to be displayed in the result. For example you may want to choose all doctors that were registered after a certain date without actually displaying the date in the result.
Query grid help. Exercise:
Call up Access97 office assistant help by pressing the F1 key or the appropriate toolbar icon. Read the following help topic 'Ways to customize a query'. The help screen is shown below.
Before we develop our first query the following sections describe a few of the tasks you need to be familiar with.
6.Running a query
Once you have specified a query you need to run it to obtain the result. You can either choose the menu option Query run or use the speed button with the exclamation mark (!) on it.
7.Saving a query
As mentioned above a query is really a definition and a result. Therefore, Access offers you the opportunity to save either of these.
7.1Saving a query definition
To do this you either choose the menu option file save when you have the query window open or click on the save button on the speed bar. In the following exercises this is the way you are expected to save the queries.
7.2Saving the results of a query
The information below is given for reference.
This is slightly more complex. Unfortunately, you can not save the results once you have obtained them. What you need to do is specify the table you wish to results to form as part of the query definition. To do this:
In the query definition window:
Choose the menu option Query Make table. The dialogue box pictured below will then pop up. Enter the name of the table you wish the results to go to. Then click OK.
8.Clearing the QBE grid and adding tables
There are two ways to do this:
Menu option Edit clear grid or Select the table(s) at the top part of the window then press delete.
Tip: To add a table to a query choose the menu option: Query add table
9.Specifying a query
The most important aspect of queries is actually specifying what they should do. You will be specifying what they do using a special query language called QBE (Query By Example). This is often referred to as query building.
The next section will demonstrate how to build select type queries. These 'select' queries, allow you to select a sub set of records from one or more tables based on certain criteria which is hardly surprising given their name! The criteria you enter into the various cells are often called expressions or statements. The doctor table will be used for all the examples and initial exercises.
10.Specifying criteria for number fields
10.1A specific value
Example: To Select all the female doctors form the doctor table.
We would set the query grid up in the followng manner. Remember we gave the doctors the following coding for gender; 1=female;2=male. Therefore, all we need to do is place a 1 in the criteria box of the gender field in the QBE grid:
Field / doc Surname / GenderSort:
Show / /
Criteria / 1
Or:
Select query Exercise 1:
Set-up the query grid in the manner shown above, that is add '1' to the criteria cell of the gender column, and then run the query. You should end up with the following result:
Result:
Doc id / doc first name / doc Surname / Gender / Date reg / Addrs title / Addrs st name / Addres st no / city / postcodeA / post
codeB / Phone No
3 / Mary / Goodall / 1 / 27/11/67 / Seeham HC / The harbour / Seaham / Se1 / 4jp / 0191 345867
4 / Fiona / black / 1 / 30/01/76 / Cragside HC / The East Wing / Cragside castle / Cr3 / 1ws / 0146 389431
5 / Anna / Scriabin / 1 / 25/03/61 / Uniiverisity MC / Newcastle Univeristy / Newcastle / Ne1 / 1aa / 0191 445776
10.2A range of values
A large number of queries search for a range of values rather than a specific one. This is achieved by using various numeric operators such as (<,>, >=, <=, >, between). For example:
Expression / Meaning>234 / Select all records with a value greater than 234
<150 / Select all records with a value less than 150
Between 1 AND 3 / Select all records between 1 and 3 inclusive
>1 / Select all records not equal to 1
11.Specifying criteria for text fields
11.1A specific value
Example: To select all the doctors with the surname goodall from the doctor table
Looking at the records in the Doctor table there are two with the surname goodall. To search for all the doctors with that name we enter goodall in the criteria box of the surname field in the QBE grid:
Field / doc firstname / SurnameSort:
Show / /
Criteria / goodall
Or:
Select query Exercise 2:
Set-up the query grid in the manner shown above, that is add 'goodall' to the criteria cell of the 'surname' column. Then run the query. Remember to clear any other criteria you may have specified from previous queries.
Result:
Doc id / doc first name / doc Surname / Gender / Date reg / Addrs title / Addrs st name / Addres st no / city / postcodeA / postcodeB / Phone No23 / mark / Goodall / 2 / 27/02/55 / Seaham HC / The harbour / Seaham / Se1 / 4jp / 0191 345867
3 / Mary / Goodall / 1 / 27/11/67 / Seeham HC / The harbour / Seaham / Se1 / 4jp / 0191 345867
Although the word goodall was entered in the criteria box without quote marks around it Access97 adds them when it runs the query. Access is actually very forgiving when you’re not quite sure how exactly to type in the query, for example all these expressions would work:
Goodall
=goodall
"goodall"
="Goodall"
Another thing to note is that, depending upon how your computer has been set-up, Access may not be case sensitive. This means that if you run a query searching for GOODALL and then another one searching for GooDAll you may end up with the same result.
11.2A range of values
As with number you can search strings using various numeric operators such as (<,>, >=, <=, >, between) can be used. For example:
Expression / Meaning>beaumont / Select all records from beaumont to the end of the alphabet
<Xandua / Select all records from Xandua to the beginning of the alphabet
Between black And jarvis / Select all records in the alphabet between black and jarvis inclusive
>Beaumont / Select all records not equal to Beaumont
Example: To select all the doctors records with a surname between black and jarvis from the doctor table would require the QBE grid to be set in the following manner:
Field / doc firstname / doc SurnameSort:
Show / /
Criteria / Between black And jarvis
Or:
Select query Exercise 3:
Set-up the query grid in the manner shown above and run the query. Remember to clear any other criteria you may have specified from previous queries.
You should end up with the following result (the records may be in a different order):
doc first name / doc Surname / ….mark / Goodall / …
steve / Jarvis / …
Mary / Goodall / …
Fiona / black / …
11.3Wild cards
A wild card is a special character you can use in an expression to search for certain patterns. A question mark (?) stands for a single character and the asterisk (*) stands for any number of characters in the same position as the asterisk.
As with the double quotation marks Access97 adds additional text to your expression when you use wild cards:
If you enter this expression / Access displays / And finds these valuesSm?th / Like "Sm?th" / Smith, Smyth
L*ng / Like "L*ng" / Ling, Levering, Long may we keep singing
*th / Like "*th" / 128th, Perth
*on* / Like "*on*" / Condiments, Confections
*/10/93 / Like "*/10/93" / All dates in October 1993
12.Specifying criteria for date fields
12.1A specific value
To retrieve all records with a specific date you type in the date you are looking for.
12.2A range of values
To retrieve a range of date values you can use one of the expressions listed below.
Expression / Meaning01/01/* / Finds all records for the first day of every year
*/02/78 / All dates in Febuary1978
*/*/78 / All records for 1978
See the section below if you wish to look for a period between two dates where either of them is the present date. To retrieve a range of dates you can use the between function i.e. Between 01/01/86 And 01/01/96. Notice that Access adds a # character to each side of the dates. It is also important to note that if you attempt to add dates with wild cards such as */12/78 you get an error message. The start and end date must be fully defined.
The following section concerning the dateadd function is for reference purposes only and can be ignored.
Reference only:
The dateadd function allows you the added flexibility of adding a particular time period - interval to a date. The method described above and in the next sub section works on days whereas this function takes into account leap years etc. When you use it you need to provide three pieces of information called parameters.
DateAdd(interval, number, date)
Parameter details:
interval this provides the interval of time you want to add to the date parameter. The Access help provides a complete list found by searching under dateadd. The most common ones are:
Number this is the number of intervals you want to add to the date. It can be positive (to get dates in the future) or negative (to get dates in the past).