What is a Query?

You use “queries” to answer a question about the information you have stored in the database tables

You can then query the results for further analysis.

Such “reports” are often based on query results which then are used to perform additional mathematical and statistical calculations.

“Queries” are also an excellent way to display information from linked tables as a single result table.

The power and flexibility of a query is actually the only reason we use database programs like Access.

They can merge records from different tables and get calculations done with a miraculous speed.

First we take a look at how the “Query wizard”, but if you base yourself too much on this tool, it will be difficult later because in practice you will more than likely have to compose queries that is something more complex than what a wizard allows us to.
There comes a time when we will have to learn how to create a query using “query design view”.

 

Using the Query Wizard

As an example, we open the “Northwind” database.

and select the “Queries” in the Objects bar.

Click on “New”.

Select “Simple Query Wizard” and click OK.

The first wizard window shows the table (s) or Query (s) selected from the Tables / Queries drop-down box from which we can select fields of data.

Let us first take the table “Categories”.

Once we have selected the table, we get to see all the fields from our table under “Available fields”.

Choose a field (in our case “Category Name”) and click the button “>”

The selected field will be added in the “Selected fields”

You can repeat this for each table whose data you want to see in the query

Let us repeat the same with the “Products” table

Here we select the “ProductName” field by clicking the “>” button again.

and click “Next”

If you have used only fields from one table or one query then you wont have to go to the next screen, so click the “Finish” button.

But in our case we have taken several fields from related tables, so we get the following screen:

In this window we have two possibilities of display:
1. “Detail” (this is obvious I think)
2. “Summary” (“Summary”): If you want to choose to summarise the field values (if they are numeric fields) then click the “Summary Options” button where you have the possibility to determine the type of summary.

In our case, none of the fields are numeric fields, so we have only the “Count records in Products” option box.
Click “OK”.
Give the query a name, for example test.

If we now run the query we get the following result:

As you can see our new query (test) was added in the database “Queries” window under objects.

If you want to delete the Query select it first, and then click the “Delete” button .

 

Create Queries

The “design view” for “queries” gives us the flexibility to design “queries”.

Although it is not the only way to make “queries”, we will still have to learn to make “queries” in the query “Design view”.

Select “Queries” in the “Objects” window.
Click on “New” and Select “Design View”.

and click “OK”.

The first thing you see is the “Show Table” window that appears above the Query Design View.

The tables from which we want to get data are placed in the top half of the query design view window.

Select the table (s) and click ‘Add’ (“Add”).
Be sure that only the tables that you actually want to use in the query are there.
If tables are not used, this gives strange results when you run the query.

If we wish to add all the fields from our table to our query, we can use the asterisk character (*) to drag the first field onto our query grid, or simply double click it.

To see the results of the query, we click the “Run Query” button.

To allow only certain fields from our table to be displayed, we drag the field from our table to a column in the “query grid.”
Also by clicking on the arrow in the field column of the “query grid”, we can choose fields to be displayed in the dropdown menu.

Result:

Of course, you can compose your query from multiple tables.

To add “Tables” to the query, we click the “Show Table” button , and select the additional “Tables” you want to add.

To remove a table from the query, we right click on the table and choose “Remove Table”.
If you forget to remove the fields from this table, the table can not be removed from the query.

If you add “tables” to the query you should be sure that there is a direct relationship among them.

If that is not the case, then you still need information from other tables that can connect these two “tables”. So you should also select and add intermediate tables in the query.

This does not work:

This works:

To remove a field from the “query grid”, we select the whole column in the grid by clicking on the top of the column and then press the “Del” key on your keyboard.

To move a field in the grid, select the field and move the mouse left or right.

If you wish to see the results of the query, clcik the “Run Query” button .

We can always return to the query “Design View” with this button. .

If you want to use the query later, click the “Save” button. .

Give the query a name and click OK.

 

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats