Queries

Queries are the searches you might be familiar with from KS3 databases or filtering in Excel. At advanced level they fall into two types:

• query by example (QBE)
• structured query language (SQL)

If you are using Access then there are several types of QBE-type queries, and you will rarely need to use SQL other than to perform a union query to combine data from two tables in one list.

Basics

If you want to find some information that isn't immediately obvious from looking at the data tables, then you need a query. This could be because there's too much data and you only want to look at a subset of it, or because you want to do some processing and summarise it. Indeed, performing a query could be seen as one of the forms of processing that turns data into information.

When you want to perform a query, there are two things you need to think about:

• What do you want to see in the results of your query? This will usually be the fields you want to display. For example, if you were to create an overdue query for a library then what you'd want to see would be which book was overdue and who'd got it.
• What criteria are you going to use to find your query results? In the overdue books example, your criteria would need to check that the due date had passed and that the book hadn't been returned.

Query by Example (QBE)

This is the standard type of query in Access. Query by example is so-called because you open the table in a graphical user interface and enter an example of the thing you're looking for.

Structured Query Language

Structured Query Language (SQL) allows you to describe in words what you want to find. It is more powerful that QBE and allows you to do certain things that you either can't do with QBE (such as Union Queries), or would be very complex or require multiple queries.

In its simplest form, an SQL query would look something like this:

SELECT forename, surname FROM tbl_student WHERE year_group = 11 ORDER BY surname, forename;

The words in upper case are SQL key words. The above query shows the forename and surname fields for all students in year 11 and orders them for surname first, then forename.

I'm not going to go into SQL in great detail - there are plenty of other sites that do this. The easiest way to experiment with SQL is to create a standard Access (QBE) query, and then change to SQL view using the button at the top left. This can also be a useful technique for quickly creating SQL to use in ASP and PHP scripts.

Union Queries

Using standard Access queries (QBE), it's quite simple to combine information from two tables into one query - provided that you want them to appear in separate columns. For example, imagine that you had a school database, with students in one table and staff in another. You can easily create a query that lists student names in one column, with the name of their tutor in the next column. But what about if you want a list of all people, with student and staff names in the same column? That's where a union query is needed.

A union query just combines the results of two queries into one list. Unfortunately, you can't do this in the standard QBE query - you need to use SQL. Fortunately, though, we can use QBE to help us. Before we combine the results of two queries, though, we need to make sure that:

• both (or all - you can combine more than two queries) queries have the same number of columns...
• AND that the columns have the same names...
• AND that the columns are in the same order

If you want to sort the combined results, however, you don't need to sort your individual queries - that would just slow down your query and make it take longer to run.

You can rename a column quite easily in a query by typing a new name, followed by a colon, immediately before the field name in the top row of the query. In the school example, you might have a student_id in the student table and a staff_id in the staff table, so to rename them to ID, for example, just amend the field names to be ID: student_id and ID: staff_id. When you view your queries, you'll notice that the column will be headed ID.

So here's what you do (sticking with the school example):

1. create two separate queries - one to find all of the staff, and one to find the students - but don't save them yet
2. change both queries to the SQL view - you'll see that the SQL will be something like this:
   1. SELECT student_id AS ID, surname, forename FROM tbl_student;
   2. SELECT staff_id AS ID, surname, forename FROM tbl_staff;
3. copy the text from one of the queries
4. delete the semi-colon (;) at the end of the other query and type the words UNION ALL
5. paste the text from the first query so that your query now something like this (don't worry that it's not all on one line):

   SELECT student_id AS ID, surname, forename FROM tbl_student UNION ALL SELECT staff_id AS ID, surname, forename FROM tbl_staff;

You can now close the first query, and don't worry about saving it, but do save the new, longer query. Run the query to check that it works - it should show the unsorted results of both queries.

If you would like your query results to be sorted, you can add the sorting to the end - before the semi-colon - e.g.

SELECT student_id AS ID, surname, forename FROM tbl_student UNION ALL SELECT staff_id AS ID, surname, forename FROM tbl_staff ORDER BY surname, forename;

Click here for a video demonstration of union queries from the Advanced ICT YouTube channel.