- Some statements cannot be easily evaluated. Such statements could be vague. Examples include: "This
person is too young", "It is going to be a sunny day tomorrow". Some other statements, although they can logically be evaluated, because of the way they are stated, they appear as vague. Examples include: "Santiago is a large country" (first of all, Santiago is not a country; even if Santiago were a country, what is the criterion for evaluating that a country is
These are the types of evaluations you make when analyzing
the records of your database.
In Lesson 2, we saw how to
create a table and how to populate it with a few records. In Lesson
3, we saw how to present the data of a table to a user but through a
form. In that lesson, we presented all the records of a table to
A query is a technique of using all data or only selecting a few
records to present to the user. Data used on a query can originate from a table, another query,
or a combination of tables and/or queries.
The universal or the most popular
language used to query a database is called the Structured Query Language
and abbreviated SQL.
||SQL can be pronounced Sequel or S. Q. L. In our lessons, we
will consider the Sequel pronunciation. For this reason, the abbreviation
will always be considered as a word, which would result in “A SQL
statement” instead of “An SQL statement". Also, in our lessons, we will
regularly write, “The SQL” instead of “The SQL" language, as the L
already represents Language.
Like most other database environments, Microsoft Access
supports SQL. Like every computer language, the SQL comes with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it.
The most fundamental word used in SQL is called SELECT.
As its name indicates, when using SELECT, you must specify what to
There are various ways you create a query in Microsoft
The Query Wizard offers the simplest approach to
creating a query where in step by step you specify the data that the query will make available. The wizard presents the tables that are part of the database and you select which
fields you need. Such a query is called a Select Query.
To use the Query Wizard, on the Ribbon, you can click
the Create tab and, in the Other section, click Query Wizard .
This would display the New Query dialog box:
On the New Query dialog box, you can click Simple
Query Wizard and click OK. The first page of the Simple Query
Wizard expects you to choose the origin of the query as a table or an
already created query.
When creating a query, in reality you create a SQL
expression but Microsoft Access takes care of creating a SQL statement behind the
scenes for you. As mentioned already, when creating a query, you must select a
table. In SQL, this is equivalent to the following formula:
SELECT What FROM WhatObject;
The FROM keyword is required. The WhatObject
of our formula is the name of the table or query you would select from the
wizard. An example would be:
SELECT What FROM Employees;
The SQL is not case-sensitive. This means that
SELECT, Select, and select represent the same word. To differentiate SQL keywords from "normal" language or from the database
objects, it is a good idea to write SQL keywords in uppercase.
A SQL statement must end with a semi-colon.
The What factor of our formula represents the field(s)
you select from a table or query.
Practical Learning: Creating a Query Using The Wizard
- Start Microsoft Access and, from the resources that accompany our
lessons, open the Bethesda Car Rental1 database
- On the Ribbon, click Create
- To create a query, in the Other tab, click the Create Wizard button
- In the New Query dialog box, click Simple Query Wizard and click OK
- In the Tables/Queries combo box, select Table: Assets
- From the Available Fields list box, double-click AssetType, Make,
Model, DateAcquired, and PurchasePrice
- Click Next twice
- Give the title AssetsInventrory and make sure the Open The Query To View Information radio button is selected
- Click Finish
Query design consists of selecting the fields that would be part of a query. We previously learned that fields could be added to a query by using the Query Wizard. Fields can also be added
by designing a query.
To proceed with this approach, the query should be displayed in Design View. You
can also write a SQL statement to select the fields for a query:
To display a query in Design View, from the Navigation Pane, you can right-click
a query and click Design View
- To start designing a new query, in the Other
section of the Create tab of the Ribbon, click Query Design
This would display
the Show Table dialog box that allows you to specify the table or query that
holds the fields you want to use in the intended query.
When a query is displaying in Design View, the Design tab of
the Ribbon displays the buttons used for a query:
Accessing the SQL Code of a Query
When a query is displaying in Design View, to access its
- On the Ribbon, you can click the arrow of the View button and click SQL
- You can right-click its title bar and click SQL View
- You can right-click anywhere in the window and click SQL View
The Show Table Dialog Box
When starting a new query, you must specify where data would come from.
If you are manually writing your SQL statement, from our previously seen syntax,
you must replace the WhatObject factor by the name of a table or query. If you
are visually creating the query, the Design View displays a list of already existing tables
in the Tables tab, and a list of already created queries in the Queries
A simple query can have its data originate from a single table. In the Show
Table dialog box, to choose the table that holds the information needed for this query, you can click that table and click Add. You can also double-click it.
After selecting the table, tables, query, or queries, you can click the Close button of the Show
Table dialog box. If the Show Tables dialog box is closed or for any reason you want to display it:
- In the Query Setup section of the Design tab of the Ribbon, you can click
the Show Table button
- You can right-click anywhere on the query window and click Show Table...
Practical Learning: Introducing Query Design
- On the Ribbon, click Create and, in the Other section, click Query
- Notice that you are presented with a list of existing tables.
On the Show Table dialog box, click Cars
- Click Add and click Close
The Query window is presented like a regular window. If the database is set to
show overlapped windows, its title bar displays its system button on the left section. This can be used to minimize, maximize, restore, move, resize, or close the window. Like all Microsoft Access window objects, the title bar displays a special menu when right-clicked:
The right section of the title bar displays the classic system buttons of a regular window.
In the top wide area of the Query window, the query displays an object (table(s), query (queries)) or a group of objects that was selected to create the query. The lower portion of the query displays boxes that would be used to perform various operations related to the query. The upper and the lower sections of the
query window are separated by a splitter bar that you can use to resize them by dragging the splitter bar up or down:
To create the fields for a query, you use the table(s) or query( queries) displayed in the upper section of the window. Once you have decided on the originating object(s), you can select which fields are relevant for your query:
- To select one field from the list, just click it
- To select many fields on the same range, you can click one of them, press and hold Shift. Then click one field on the other end of the desired
- To select fields at random, click one of the desired fields, press and hold Ctrl; then click each one of the desired fields
- To select all fields, you can click the * line on the list of
To make a field participate in a query, you have
- Once you have made your selection on the list in the top part of the query
window, you can
drag it and drop it in the bottom section of the query window
- Instead of dragging a field or all fields,
you can either double-click a field to add it to the query, or double-click
the line with * to add all fields to the query
- In the bottom part of the query window, click an empty Field box to show a
combo box. Then click the arrow of that combo box and select an item from
In the SQL, to add one column to a statement, replace the
What factor of our formula with the name of the column. An example would be:
SELECT FirstName FROM Employees;
If you want to include more than one field from the same table, separate them with a comma. For example, to select the first and last names of
a table named Employees, you would write the statement as follows:
SELECT FirstName, LastName FROM Employees;
To include everything from the originating table or query, use the asterisk * as the
What factor of our formula. Here is a statement that results in including all fields from the
SELECT * FROM Employees;
The name of a field can be delimited by square brackets to reduce confusion in case the name is made of more than one word.
The square brackets provide a safeguard even if the name is in one word. Based on this, to create a statement that includes the first and last names of a
table named Employees, you can write it as follows:
SELECT [FirstName], [LastName] FROM [Employees];
To identify a field as belonging to a specific table or query,
you can associate its name to the parent object. This association is referred to as qualification. To qualify a field,
type the name of the object that is holding the field, then add a period followed by the name of the field. The basic syntax of a
SELECT statement would be:
SELECT WhatObject.WhatField FROM WhatObject;
Imagine you want to get a list of people by their last names from data stored in
the Employees table. Using this syntax, you can write the statement as follows:
SELECT Employees.LastName FROM Employees;
SELECT [Employees].[LastName] FROM [Employees];
In the same way, if you want to include many fields from the same table, qualify each and separate them with a comma. To list the first and last names of the records from the
Employees table, you can use the following statement:
SELECT Employees.FirstName, Employees.LastName FROM Employees;
SELECT [Employees].[FirstName], [Employees].[LastName] FROM [Employees];
If you want to include everything from a table or another query, you can qualify the * field as you would any other field. Here is an example:
SELECT Employees.* FROM Employees;
SELECT [Employees].* FROM [Employees];
You can also use a combination of fields that use square
brackets and those that do not:
SELECT FirstName, [LastName] FROM Employees;
The most important rule is that any column whose name is in
more than one word must be included in square brackets.
You can also use a combination of fields that are qualified
and those that are not
SELECT [Employees].[FirstName], LastName FROM [Employees];
Practical Learning: Selecting Fields to Build a Query
- From the list of fields, click and drag Make, then drop it anywhere on the first empty field on the lower section of the view:
- Click Car Year
- Press and hold Shift, then click Doors and release Shift. Notice that three fields have been
- Drag the group of items and drop it on the empty column right to Make
- Notice all the selected fields that have been added to the query
- To view the SQL code of the query, right-click its title bar and
click SQL View
SELECT Cars.Make, Cars.[Car Year], Cars.Category, Cars.Doors
- To save and close the query, right-click its title bar and click
- When asked whether you want to save the query, click Yes
- Type Cars Information as the name of the query and press Enter
In the Navigation Pane, a query is represented by an icon
and a name.
Executing a query consists of viewing its results but the
action or outcome may depend on the type of query. To view the result of a
- If the query is currently closed, from the Navigation Pane:
- You can double-click it
- You can right-click it and click Open
- If the query is already opened and it is in Design View, on the Ribbon:
- You can click the Run button
- You can click the View button
or you can click the arrow of the View button and click Datasheet View
If you manually write a SQL statement and want to execute
it, change the view to Datasheet View.
Practical Learning: Executing a Query
- In the Navigation Pane, double-click Cars Information to preview the
- Right-click the title bar of the Query window and click SQL View
- Change the code as follows:
- To execute the query, right-click the window's title bar and click
- Save and close the query
Sometimes, the idea of using a query is to test data or verify a condition. Therefore, a
query can provide just a temporary means of studying information on your database. When performing
the assignment or when testing values before isolating an appropriate list, you can add, insert, delete, replace or move fields at will. We have already covered different techniques of adding or inserting
fields, from the Query Wizard or from a list of fields in the top section of the
query window. Some other operations require that you select a column from the
bottom section of the query window:
- To select a field in the lower section of the view, click the tiny bar of the column
The whole column will be selected
- To select a range of columns, click the column header of one at one end, press and hold Shift, then click the column header at the other end
Since selecting a column in the Query window is a visual
operation, there is no equivalent in SQL.
Removing a Column From a Query
As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a column anymore on a query,
which happens regularly during data analysis, you can either delete it or replace it with another
- To delete a column:
- Once it is selected, you can press Delete
- Right-click the column header and click Cut
- To delete a group of columns, select them and press Delete
To remove a column from a SQL statement, simply delete it.
An example would be:
SELECT EmployeeName, DateHired, Title FROM Employees;
SELECT EmployeeName, Title FROM Employees;
To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:
To replace a column from a SQL statement, simply change its
name to the name of another existing column of the same table or query. An example would
SELECT EmployeeName, DateHired, Title, Salary FROM Employees;
SELECT EmployeeName, DateHired, EmailAddress, Salary FROM Employees;
Columns on a query are positioned incrementally as they are added to it. If you do not like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of
columns, you must first select it. Then:
- To move a field, click its column header once. Click it again and hold your
mouse down, and drag in the direction on your choice
- To move a group of columns, first select the group and then proceed as if it were one column
Since moving a column in the query window is a visual
operation, there is no equivalent in SQL. Otherwise, in the SQL statement, you
can either edit the statement or delete the field in one section to put it in
another section. An example would be:
SELECT EmployeeName, DateHired, EmployeeNumber, Salary FROM Employees;
SELECT EmployeeNumber, EmployeeName, DateHired, Salary FROM Employees;
Practical Learning: Manipulating Fields
- The Bethesda Car Rental1 database should still be opened.
On the Ribbon, click Create and, in the Other section, click Query Design
- In the Show Table dialog box, double-click Cars and click Close
- In the list of fields, double-click Make, Model, Doors, Category,
Available, and Condition
- To preview the query, on the Ribbon, click the View button
- After viewing the query, to switch back to Design View, on the
Ribbon, click the View button
- To replace a field, in the lower section of the Design View, click
Doors and notice that an arrow of a combo box appears
- Click the arrow of the Doors combo box and select Car Year
- Scroll to the right on the lower section of the view to display the first empty field
- To add a new field, in the lower section of the query window, click
Condition and press Tab. Notice the combo
- Press and hold Alt, then press the down arrow key and release Alt.
This displays the list of the combo box
- Press the down arrow key a few times until Picture is selected, then
- Scroll back to the left
- To insert a field, drag Tag Number from the Cars list and drop it on top of
- Notice that the newly inserted field has been added to the left of the field it was dropped on
(In the same way, you can select various fields and decide to insert them to the left of a field of your
- Scroll to the right side of the lower section of the view until you can see Picture.
To delete a field, in the lower section of the view, position the mouse on the tinny horizontal bar above Picture until the mouse turns into a down pointing arrow:
- Click. Notice that the whole column is selected
- Press Delete. Notice that the field is removed from the query
- To view the query, click the View button
- To switch the query back to Design View, on the ribbon, click the View
- Scroll back to the left of the lower view and make sure you can see
the Tag Number and the Make columns
In the lower section of the view, click the bar on top of Tag Number and release the
- Click the Tag Number header bar again and hold your mouse down
- Notice a vertical line that guides you. Drag left until the vertical guiding line gets between Make and Model:
- Release the mouse
- To view the query, on the Ribbon, click the View
- Switch the query back to Design View
- Click and hold your mouse on the bar on top of Category. Then drag right to
Condition to select the Category, the Available, and the Condition columns. Then release the mouse
- Click and hold your mouse again on the bar on top of Category
- Drag left until the vertical line is between the Tag Number and the
- Then release the mouse
- To run the query, right-click its title bar and click Datasheet View
- To close the query, double-click its system icon
- A message asks you whether you want to save the query, click No (this was a test; it was just a test…)
A query uses the same approach of
a table to present its data: it is made of columns and rows whose intersections
are cells. Although the main purpose of a query is to either prepare data for
analysis or to isolate some fields to make them available to other database objects,
as done on a table, data can be entered in a query.
Data entry on a query is done the same as on a table:
data is entered into cells. The Enter, Tab and arrow keys are used with the
same functionality. Like a table, a query provides navigation buttons on
its lower section, allowing you to move to the first, the previous, the next,
the last or any record in the range of those available.
In Lesson 26, we will
see how to perform data entry using SQL.
Practical Learning: Performing Data Entry on a Query
- From the Navigation Pane, double-click the Assets Inventory query to
- Click the first empty field under the Asset Type column
- Type Computer and press Enter
- Complete the query as follows:
|| Date Acquired
|| Purchase Price
|| LaserJet 4200dtn
|| B/W Printer
|| NetVista M42
|| Business Notebook
|| Phaser 8200
|| Color Printer
|| ThinkCentre S50
|| Ctrl + '
|| Ctrl + '
Ctrl + '
|| Ctrl + '
|| Ctrl + '
|| Ctrl + '
|| Ctrl + '
- After using the query, close it
Like tables, queries provide you with a fast means of printing data.
Once again, this should be done when you need a printed sheet but not a
professionally printed document. Data printing on a query is done with the exact same approaches and techniques
as for a table.
A Query or a SQL Statement as a Record Source
A Query as a Record Source
When creating a form or a report that would be used to present data to the user, we selected a table as the
source of data. In the same way, if you have created a query that holds some
records, you can use it as the base of data for a form or report.
If you delete the form or report, the query would
still exist because it is a separate object, but it would lose its data holder.
Practical Learning: Specifying a Query as a Record Source
- On the Ribbon, click Create and, in the Forms section, click Form Design
- Double-click the intersection of the rulers to access the Properties
window for the form
- In the Properties window, click the Data tab and click Record Source.
Click the arrow of its combo box and select Assets Inventory
- Right-click the title bar of the form and click Save
- Type Assets Inventory and click OK
- Design the form as you want. Here is an example:
- Preview the form
- Close the form
- When asked whether you want to save the change, click Yes
Using a SQL Statement as a Record Source
Instead of first formally creating a query before using it
for a form or report, you can select the data from a table or a query and use it on the
form or report. Behind the scenes, Microsoft Access would create the SQL
expression that is directly applied to the form or report. Such a query is
not saved as an object. This technique is used when you do not need a formal
query as the base of data for a form or report.
There are various techniques you can use to create a query
specifically made for a form or a report:
- You can start the Form Wizard or the Report Wizard. Then, in the first
page of the wizard, select the
Tables/Queries that holds the data, and complete the wizard. When you finish
with the wizard, a SQL statement would be created for the form or report
- You can start by displaying a form or report in Design View. Then, in the Properties
window, click Record Source and click its ellipsis button .
This would open the Query Builder window where you can select a table or a
query from the Show Table dialog box and proceed to building a query
After creating the form or report, if you delete it
(the form or the report), the expression would be lost also.
Practical Learning: Using a SQL Statement as a Record Source
- On the ribbon, click Create and, in the Reports, click Report Design
- Right-click the title bar of the report and click Save
- Type Employees Payroll Information and press Enter
- Double-click the intersection of the rulers to access the Properties
window of the report
- In the Data tab of the Properties window, click Record Source and click
its browse button
- In the Show Table dialog box, click Employees
- Click Add and click Close
- In the list of the Employees window, double-click EmployeeNumber,
FirstName, LastName, Title, and BillingRate
- To see the SQL statement, right-click the Query Builder title bar and
click SQL View
SELECT Employees.EmployeeNumber, Employees.FirstName,
Employees.LastName, Employees.Title, Employees.BillingRate
- To see the result, right-click the title bar and click Datasheet View
- Right-click the title bar and click Close
- When asked whether you want to save, read the whole message box and click
- Notice that the Record Source is filled with a SELECT statement.
Design the report as you see fit
- Print Preview the report
- Save and close the report
MCAS: Using Microsoft Office Access 2007 Topics
- Open the Yugo National1 Bank
- Use the Simple Query Wizard to create a query based on the Customers
and that includes the AccountNumber and CustomerName fields. Save the
query as Customers Accounts
- Use the Design View to create a query based on the Employees table
and that includes the following fields: EmployeeNumber, LastName,
FirstName, and Title. Save the query as Employees Identification
- Open the Watts A Loan1 database
- Use the Simple Query Wizard to create a query based on the Employees table
and that includes the following fields: FirstName, LastName, City, and
HomePhone. Save the query as Employees Contact Information
- Open the World Statistics1 database
- Use the Simple Query Wizard to create a query that includes the CommonName,
the Nationality, and the Capital fields of the Countries table. Save the
query as Countries Identities
- Using the Design View of the Countries Identities query to add the Independence
and the NationalHoliday fields
- Save and close the query
- Open the US Senate1 database
- Use the Design View to create a query based on the Senators table and
populate it with SenatorName, Party, State, and WebSite
- Save the query as Senators Identifications and close it