Queries

When you have your tables filled with data then the next thing is getting meaningful information out of them and this is where you use queries. There are a number of different types or query and below is a brief description of what each one does. After the descriptions I'll go through an example of using each type and eventually we will build up a number of queries we can then use in our application.

Select Query
This is the query type you will likely use the most. It is used, as the name suggests, to select data from one or more table and return the results in a grid similar to a spreadsheet. You can sort data in alphabetical order or aggregate data to perform mathematical functions.

Make Table
Also known as a create table query, this type is similar to a select query but instead of just displaying your results in a grid a new table is created with the data from the select query.

Append Query
If you have used a make table query to create a table, you can append new data to it by using an append query. Again, it is similar to a select query but the results will be added to the end of the table you specify in the query design.

Crosstab Query
This is another select query but the results are pivoted so you have columnar data from one of your fields.

Delete Query
This query is used to delete data from an existing table based on the criteria of your query.

Update Query
If you want to change data in a column or a field then you use an update query. Again, this is designed like a select query but you can set criteria and update only those values you choose to.

Designing our First Query

From your customised menu bar click the Query icon or from the Create tab on the main ribbon, click Query Design. You'll see a form like the one shown below. The top of the window is where you add the tables you want to query data from and below is the column and criteria grid. It may look complicated at first but you'll soon see it's quite intuitive and will become second nature quickly.

We'll create a query that lets us view data from our Artist table. There a few methods for getting the table into the query designer. The easiest is to drag the Artist table from the Object list on the left into the upper part of the window. When you have the table displayed in the designer double click anywhere on the top bar of the table (clicking on the name Artist will do) and this will select all of the fields in our table. Click a field name and drag it down onto the grid which will place all of our fields in the column/criteria grid like below.

From the menu bar, click the Run icon which looks like a large red exclamation mark. You will be presented with a list of all of your artist information from the Artist table.

The results are returned in ArtistID order, but say we had a thousand artist names in the table we might want to see them in alphabetical order of the artist name.