Fundamentals of Queries and Data Selection

Introduction

After creating one or more tables in a database and populating it (them) with values, you can examine or analyze the values in the database. Analyzing the values in a table is also referred to as querying. One of the objects uses is called a query

To query the records of a database, you can use Boolean algebra combined with some operators. Boolean Algebra works on logical statements. A statement is a sentence that acknowledges a fact or a possibility. That fact is eventually evaluated as being true or false.

A query is a technique of selecting all or some data to present to the user, to use the data in an expression, or to use the record(s) one way or another. Data used on a query can originate from a table, from another query, or from a combination of (a) table(s) and/or (a) query (queries).

In Microsoft Access, data analysis can be performed on a table, a form, a query, or a report. You can also send the data to Microsoft Excel for the same goal, either because you prefer, you are more familiar with, or you believe that the other application has better tools.

To analyze data in Microsoft Access, display the table or query in Datasheet View, the form in Form View, or the report in Report View. To assist you with data analysis, when a table, a query, a form, or a report is displaying in their regular view, the Ribbon is equipped with a section titled Sort & Filter in the Home tab:

Practical Learning: Introducing Queries

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Cruise2 database

Introduction to Query Design

Query design consists of selecting the fields that would be part of a query. Fields can be added by designing a query.

To start designing a new query, on the Ribbon, click the Create tab. In the Queries section, click the Query Design button . This would display the Show Table dialog box.

Practical Learning: Starting a Query

1. On the Ribbon, click Create
2. To start a new query, in the Queries section, click the Query Design button

The Show Table Dialog Box

When starting a new query, you must specify where data would come from. If you are visually creating the query, the Show Table dialog box displays a list of existing tables in the Tables tab:

The Queries tab shows a list of already created queries in the Queries property page.

A simple query can have its data originate from a single table or an existing query. If you are using the Show Table dialog box, to choose the table or query that holds the information needed for this query, click the tab of the category. Then:

• Double-click the table or query
• Click the table or query and click Add

When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query:

After selecting a table, some tables, a query, or some queries from the Show Table dialog box, you can click the Close button of the dialog box. If the Show Table 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, click the Show Table button
• Right-click anywhere on the query window and click Show Table...

Practical Learning: Introducing Query Design

1. On the Show Table dialog box, make sure Cabins is selected. If not, click it.
   Click Add
2. Click Close

The Query Window

When designing a query, you use a window named the Query window. If the database is set to show overlapped windows, the query's 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 tab or the title bar displays a special menu when right-clicked.

In the top wide area of the Query window, the query displays an object ((a) table(s), (a) query (queries)) or a group of objects that was (were) selected to create the query. The lower portion of the query displays various boxes. The upper and the lower sections of the query window are separated by a splitter bar that you can use to resize them:

Adding a Field to a Query

To make a field participate in a query, you have various options:

• Drag it from the list and drop it in the bottom section of the query window
• Double-click a field from the list
• In the bottom part of the query window, click an empty Field box to display a combo box. Then click the arrow of that combo box and select the desired item from the list:

Practical Learning: Adding a Field to a Query

• From the list in the top part of the window, drag RoomNumber and drop it in the first column in the bottom part of the window:

Executing a Query

Executing a query consists of viewing its results. The action or outcome may depend on the type of query. To view the result of a query:

• If the query is currently closed, in the Navigation Pane:
  • Right-click the query and click Open
  • Double-click the query
• If the query is currently opened in the Design View:
  • On the Ribbon, click the Run button
  • On the Ribbon, click the View button
  • On the Ribbon, click the arrow of the View button and click Datasheet View
  • On the status bar of the Query window, click the Datasheet View button

If you had manually written a SQL statement and want to execute it, change the view to Datasheet View.

Practical Learning: Executing a Query

1. In the Results section of the Ribbon, click the Run button :

   

2. Close the Query window
3. When asked whether you want to save, click No

Managing a Query

Saving a Query

As mentioned already and as we will see in different lessons and sections, there are various ways you can use a query or a SQL statement. For example, you can start a query simply to see some records or to test a condition. If you plan to use a query many times, you should save it. This is done the same way as for a table:

• Right-click the tab or title bar of the window
• Click the Save button on the title bar of Microsoft Access
• If you are viewing the query, close it

If the query was not previously saved, you would be asked whether you want to save it. You will have to name the query. Unlike a form or report, the name of a query must be different from the name of any table of the same database.

To display a query in Design View, from the Navigation Pane, right-click the query and click Design View.

Practical Learning: Saving a Query

1. On the Ribbon, click File and click Open
2. In the list, click Chemistry2 from Lesson 25
3. On the Ribbon, click Create and click Query Design
4. On the Show Table dialog box, make sure Elements is selected.
   Click Add and click Close
5. In the top portion of the window, double-click Symbol to add it to the query
6. Close the Query window
7. When asked whether you want to save, click Yes
8. Set the name as Primary Information and click OK

The Query Builder

After saving a query, it is represented in the Navigation Pane by an icon and a name.

The Properties of a Table

To let you get some information about a query, Microsoft Access provides a dialog box that allows you to know the name of the table you are accessing as well as the date and time when it was created or modified.

To display the properties of a query, right-click the query in the Navigation Pane and click Object Properties. Here is an example:

The Property Sheet of a Query

In the Design View of a query, Microsoft Access provides a Property Sheet window that allows you to specify or manage some characteristics. To display the Property Sheet of a query, right-click any part of the query in Design View and click Properties.

Unlike the table, the content of the Property Sheet depends on the area of the query that is being accessed. To access the properties of the query itselft, after displaying the Property Sheet, click an empty area in the top section of the Query window. Here is an example:

Notice that the Property Sheet has only one tab. To display the properties of a field, click it in the bottom section of the window:

This time, the Property Sheet has two tabs.

Introduction to SQL and Queries

Querying in SQL

Querying a database is equivalent to selecting records or their values. To support this operation, the most fundamental word used in SQL is called SELECT. As its name indicates, when using SELECT, you must specify what to select.

To select records, you create a SQL expression. The most basic formula to follow is:

SELECT what FROM what-object[;]

The SELECT and the FROM keywords are required. The what-object part is the name of a table or a query.

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. An example would be:

SELECT what FROM Employees

As an option, you can end a SQL statement with a semicolon but it is not required.

In the SQL, to add a 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;

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. Here is an example:

SELECT [FirstName] FROM Employees;

The name of the table or query can also be delimited by square brackets. Here is an example:

SELECT [FirstName] FROM [Employees];

Opening the SQL Code of a Query

When a query is displaying in Design View, to access its code:

• On the Ribbon, click the arrow of the View button and click SQL View

  

• Right-click the tab or the title bar of the window and click SQL View
• Right-click anywhere in the window and click SQL View
• On the status bar of the window, click the SQL View button

Practical Learning: Introducing SQL Code

1. On the Ribbon, click Create and click the Query Design button
2. In the Show Table dialog box, click Close
3. Right-click the body of the window and click SQL View
4. Change the code as follows:

   SELECT RoomNumber FROM Cabins;

5. In the Views section of the Ribbon, click the View button :

   

6. Right-click the title bar of the window and click SQL View

Distinct Field Selection

If you specify a column to select from a table (or query), every record would come up. This can result in repeated records. Sometimes you want to show each value only once.

In the SQL, to get a list of non-repeating values, put the DISTINCT keyword between SELECT and the name of the column.

Practical Learning: Introducing SQL Code

1. Change the code as follows:

   SELECT CabinType FROM Cabins;

2. In the Results section of the Ribbon, click the Run button :

   

3. Notice that some values display more than once. Also notice the total number of records.
   Change the code as follows:

   SELECT DISTINCT CabinType FROM Cabins;

4. In the Results section of the Ribbon, click the Run button :

   

5. Close the Query window
6. When asked whether you want to save, click No

Adding Many Fields to a Query

Introduction

To specify the fields of a query, use the table(s) or query(queries) displayed in the upper section of the Query window. From their lists, you can select which fields are relevant for your query. You select the fields using the same techniques we have used for the Field List of a form or report, using the mouse, the Ctrl, and the Shift keys of the the keyboard.

PPractical Learning: Selecting Fields to Build a Query

1. On the Ribbon, click Create
2. To start a new query, in the Queries section, click the Query Design button
3. In the Show Table dialog box, make sure Cabins is selected.
   Click Add and click Close
4. From the list of fields, click CabinType
5. Press and hold Shift
6. Click Rate2Passengers
7. Release Shift
8. Drag the selection to the bottom side of the window to drop and release the mouse:

   

   

9. In the bottom side of the window, if necessary, scroll to the right to display an empty column in the bottom-right side.
   In the top side of the window, click RoomNumber
10. Press and hold Ctrl
11. Click Available
12. Release Ctrl
13. Drag the selection to the empty column in the bottom side of the window and release the mouse:

    

    

14. To see the results, in the Results section of the Ribbon, click the Run button :

    

15. To close and save the query, right-click its title bar and click Close
16. When asked whether you want to save the query, click Yes
17. Type Rooms Analysis as the name of the query and press Enter
18. On the Ribbon, click File and click Open
19. In the list, click Chemistry2
20. In the Navigation Pane, right-click the Primary Information query and click Design View
21. From the top list, drag AtomicNumber and drop it on the Symbol column at the bottom

    

22. In the top list, double-click ElementName and AtomicWeight

    

23. Close the query
24. When asked whether you want to save, click Yes

Selecting Fields in SQL

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 make your code easy to read, and especially if the statement is very long, you can put it in different lines. The primary approach is to put the SELECT statement on its own line and the FROM clause on its own line.

A second technique is to put each field name on its own line. Here is an example:

SELECT FirstName,
       LastName
FROM Employees

In fact, the comma of each field that has one can be put on the next line, preceding the next field. Here is an example:

SELECT FirstName
       ,LastName
       ,Gender
       ,DateOfBirth
FROM Employees

Practical Learning: Using the Print Preview of a Report

1. On the Ribbon, click Create and click Query Design
2. On the Show Table dialog box, click Close
3. Right-click the title bar of the window and click SQL View
4. Change the code as follows:

   SELECT FirstName, LastName FROM Employees;

5. To see the results, in the Results section section of the Ribbon, click the Run button
6. On the status bar of the Query window, click the SQL button and change the code as follws:

   SELECT EmployeeNumber,
          FirstName,
          LastName
   FROM Employees;

7. To see the results, in status bar of the window, click the Datasheet View button
8. Close the Query window
9. When asked whether you want to save, click No

Introduction to the Query Wizard

Probably the easiest way to create a query is by using a wizard. As seen with other categories of objects (forms and reports), Microsoft Access provides a wizard to help you easily create a query. The wizard presents the tables and queries of the database so you can select the fields you need.

To use the Query Wizard, on the Ribbon, click the Create tab. In the Queries section, click Query Wizard . This would display the New Query dialog box:

On the New Query dialog box, 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.

After creating a query using the Simple Query Wizard, it becomes a normal query like one you had designed. This means that you can manage its fields any way you like.

Practical Learning: Creating a Query Using The Wizard

1. On the Ribbon, click File and click Open
2. In the list of files, click StatesStatistics2 from the previous lesson
3. On the Ribbon, click Create
4. To create a query, in the Queries section, click the Query Wizard button
5. In the New Query dialog box, click Simple Query Wizard if necessary, and click OK
6. In the Tables/Queries combo box, make sure Table: States is selected. If not, select it.
   In the Available Fields list box, double-click StateName, AreaSqrMiles, AdmissionUnionDate, Region, and Capital

   

7. Click Next
8. Accept the Detail selection in the wizard page and click Next
9. Change the name of the query to States Analysis

   

10. Click Finish

    

Selecting All Fields

To include everything from the originating table or query:

• If you are designing the query, double-click * in the list of fiels
  • Select all fields in the table and drag the selection to the bottom part of the window
• In your SQL statement, use the asterisk * as the what factor of our formula

Here is a statement that results in including all fields from the Employees table:

SELECT * FROM Employees;

Practical Learning: Selecting All Fields for a Query

1. On the Ribbon, click File and click Open
2. From the resources that accompany these lessons, open the FunDS3 database
3. On the Ribbon, click Create
4. To create a query, in the Queries section, click the Query Design button
5. In the Show Table dialog box, double-click StoreItems
6. Click Close
7. From the list, drag the asterisk * and drop it in the bottom part of the window

   

   

8. On the Ribbon, click the View button
9. After viewing the records, close the query
10. When asked whether you want to save, click Yes
11. Set the name as Inventory Evaluation and click OK

Qualifying the Names of Fields

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 what-object.what-field FROM what-object[;]

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;

Or

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;

Or

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;

Or

SELECT [Employees].* FROM [Employees];

You can also use a combination of fields that use square brackets and those that do not. Here is an example:

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. Here is an example:

SELECT [Employees].[FirstName], LastName FROM [Employees];

Practical Learning: Ending the Lesson

• Close Microsoft Access

Previous	Copyright © 2002-2021, FunctionX	Next