What does a Query do?
You use “Queries” to answer a question about the data you have stored in the database tables. You can use the results from the “Query” for further analysis. Reports for example are often based on “Query” results, which 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 only reason we use database programs like “Access”, is because of the power and flexibility of a “Query”. They can merge records from different tables with a miraculous speed and also perform calculations.
First we take a look at the “Query Wizard” but, do not depend too much on this tool because in practice you will more than likely have to compose “Queries” that are more complex than what a wizard does.
A “Query” can be made in a number of ways.
A first way is by using the “Query Wizard “. Select the “Create” tab in the “Ribbon” and click the “Query Wizard”.
This opens the “New Query” dialog.
On the left of this dialog box, we find a small description of the type of wizard that we have selected on the right side of the dialog.
We can choose from four types of “Queries”:
The first, “Simple Query”, creates a “Query”, from the selected fields of one or more tables.
The second option, “Crosstab Query” allows us to create a crosstab “Query” of the selected fields in a compact form like an “Excel” worksheet.
The third option, “Find Duplicates Query” is a “Query” that searches records with duplicate field values in a single table.
The last possibility, “Find unmatched Query” creates a “Query” that finds records that have no related records in another table.
In this example I choose the first option “Simple Query” and click the OK button.
This opens the “Simple Query Wizard” window
In the top box, choose a table where you want to make a “Query” from. Just click the downward pointing arrow and choose a table or “Query”. Since we currently have no “Query”, there will be only tables in this list.
In the “Available fields” we see all the fields from the table we just selected. Select the fields you want in your “Query” and click the button with the ‘>’ sign. If you wish to add all the fields from this table into your “Query”, click the button with the ‘>>’ sign.
To remove fields from the “Query”, select the field in the right side of the dialog box and click the button with the ‘<‘ sign
To delete all columns, click the button with the ‘<<‘ button.
In this example I want to use fields from two tables, namely the “Album” table and the “Song” table.
In the “Album” table, I want the fields “Type”, “Album Title” and “Location”.
In the “Lyrics” table, I want the fields “Song Title”, “Genre” and “Date”. So I select the “Album” table first in the “Tables / Queries” and place fields from this table under the “Available fields” box to the “Selected Fields” box.
Then I choose the “Song” table in the “Tables / Queries” and place the above mentioned fields from this table in the “Available Fields” box to the “Selected Fields” box.
Click the “Next” button in the dialog. In the second window, we must decide whether we want a detail “Query” or summary.
I choose the first option “Detail( shows every field of every record).” The second option, “Summary”, is available only when you have a numeric field in a table ( I give an example of this in the next lesson).
Click the “Next” button.
In the third window of the “Query”, we give it an appropriate name in the top box .
Select the checkbox “Open the query to view information.” I will discuss about the “Modify the query design” option, in the next lesson.
Click the “Finish” button. The newly created “Query” is found in the navigation pane. When we open, we see that all fields that we have just selected from the two tables, displayed in this “Query”.
You've completed Lesson 21 START NEXT LESSON