Introduction to Data Analysis

Querying a Database

	Introduction

After creating one or more tables in a database and populating it (them) with values, one of the next steps you would take is to examine or analyze the values in the database. Analyzing the values in a table is also referred to as querying.

Some statements are true at one time but can change to false at another time. Examples include: "Melbourne is the capital of Australia", "It is now 12:00 PM", or "It is raining" 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 large?) These are the types of evaluations you make when analyzing the records of your database. Introduction to SQL

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 user. 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 select.

There are various ways you create a query in Microsoft Access.

The Query Wizard

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.

1. Start Microsoft Access and, from the resources that accompany our lessons, open the Bethesda Car Rental1 database
2. On the Ribbon, click Create
3. To create a query, in the Other tab, click the Create Wizard button
4. In the New Query dialog box, click Simple Query Wizard and click OK
5. In the Tables/Queries combo box, select Table: Assets
6. From the Available Fields list box, double-click AssetType, Make, Model, DateAcquired, and PurchasePrice
    
   
7. Click Next twice
8. Give the title AssetsInventrory and make sure the Open The Query To View Information radio button is selected
    
   
9. Click Finish

Introduction

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 code:

• On the Ribbon, you can click the arrow of the View button and click SQL View
• 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 property page:

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

1. On the Ribbon, click Create and, in the Other section, click Query Design
2. Notice that you are presented with a list of existing tables.
   On the Show Table dialog box, click Cars
3. Click Add and click Close

The Query Window

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:

Selecting the Columns

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 range
• 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 fields

To Add Columns

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

• 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 the list:

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 Employees table:

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;

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:

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

1. 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:
    
   
2. Click Car Year
3. Press and hold Shift, then click Doors and release Shift. Notice that three fields have been selected
4. Drag the group of items and drop it on the empty column right to Make
    
   
5. Notice all the selected fields that have been added to the query
    
   
6. 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 FROM Cars;

7. To save and close the query, right-click its title bar and click Close
8. When asked whether you want to save the query, click Yes
9. Type Cars Information as the name of the query and press Enter

Executing a Query

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 query:

• 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

1. In the Navigation Pane, double-click Cars Information to preview the query
2. Right-click the title bar of the Query window and click SQL View
3. Change the code as follows:
    

   SELECT Make, Model, [Car Year], Category, Available, Condition FROM Cars;

4. To execute the query, right-click the window's title bar and click Datasheet View
5. Save and close the query

Selecting a Column

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 header:
   
  
  
  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 column:

• 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;

To

SELECT EmployeeName, Title FROM Employees;

Replacing a Column

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 be:

SELECT EmployeeName, DateHired, Title, Salary FROM Employees;

To

SELECT EmployeeName, DateHired, EmailAddress, Salary FROM Employees;

Moving a Column

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;

To

SELECT EmployeeNumber, EmployeeName, DateHired, Salary FROM Employees;

Practical Learning: Manipulating Fields

1. The Bethesda Car Rental1 database should still be opened.
   On the Ribbon, click Create and, in the Other section, click Query Design
2. In the Show Table dialog box, double-click Cars and click Close
3. In the list of fields, double-click Make, Model, Doors, Category, Available, and Condition
4. To preview the query, on the Ribbon, click the View button
5. After viewing the query, to switch back to Design View, on the Ribbon, click the View button 
6. To replace a field, in the lower section of the Design View, click Doors and notice that an arrow of a combo box appears
7. Click the arrow of the Doors combo box and select Car Year
    
   
8. Scroll to the right on the lower section of the view to display the first empty field
9. To add a new field, in the lower section of the query window, click Condition and press Tab. Notice the combo box
10. Press and hold Alt, then press the down arrow key and release Alt. This displays the list of the combo box
11. Press the down arrow key a few times until Picture is selected, then press Enter
12. Scroll back to the left
13. To insert a field, drag Tag Number from the Cars list and drop it on top of Category
14. 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 choice)
15. 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:
     
    
16. Click. Notice that the whole column is selected
17. Press Delete. Notice that the field is removed from the query
18. To view the query, click the View button
19. To switch the query back to Design View, on the ribbon, click the View button
20. 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 mouse
21. Click the Tag Number header bar again and hold your mouse down
22. Notice a vertical line that guides you. Drag left until the vertical guiding line gets between Make and Model:
     
    
23. Release the mouse 
24. To view the query, on the Ribbon, click the View button
25. Switch the query back to Design View
26. 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 
27. Click and hold your mouse again on the bar on top of Category
28. Drag left until the vertical line is between the Tag Number and the Model columns
29. Then release the mouse
30. To run the query, right-click its title bar and click Datasheet View 
31. To close the query, double-click its system icon
32. A message asks you whether you want to save the query, click No (this was a test; it was just a test…)

Data Entry on a Query

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

1. From the Navigation Pane, double-click the Assets Inventory query to open it
2. Click the first empty field under the Asset Type column
3. Type Computer and press Enter
4. Complete the query as follows:
    

   AssetType	Asset Tag	Make	Model	Date Acquired	Purchase Price	Notes
   Printer	2295-7978	HP	LaserJet 4200dtn	10/08/2007	1950.95	B/W Printer
   Computer	8308-4504	IBM	NetVista M42	10/08/2007	1035.00
   Laptop	2592-0708	Gateway	200XL	12/05/2007	2095.95	Business Notebook
   Printer	2874-1587	Xerox	Phaser 8200	10/22/2007	1250.55	Color Printer
   Digital Camera	2304-5802	Olympus	C-50	11/06/2007	450.75
   Computer	4059-2749	IBM	ThinkCentre S50	10/08/2007	1055.55
   Computer	2848-5813	Ctrl + '	Ctrl + '	10/20/2007	1120.55
   Ctrl + '	9284-3058	Ctrl + '	Ctrl + '	Ctrl + '	Ctrl + '

5. After using the query, close it

Query Printing

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 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

1. On the Ribbon, click Create and, in the Forms section, click Form Design
2. Double-click the intersection of the rulers to access the Properties window for the form
3. In the Properties window, click the Data tab and click Record Source. Click the arrow of its combo box and select Assets Inventory
4. Right-click the title bar of the form and click Save
5. Type Assets Inventory and click OK
6. Design the form as you want. Here is an example:
    
   
7. Preview the form
    
   
8. Close the form
9. 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

1. On the ribbon, click Create and, in the Reports, click Report Design
2. Right-click the title bar of the report and click Save
3. Type Employees Payroll Information and press Enter
4. Double-click the intersection of the rulers to access the Properties window of the report
5. In the Data tab of the Properties window, click Record Source and click its browse button
6. In the Show Table dialog box, click Employees
7. Click Add and click Close
8. In the list of the Employees window, double-click EmployeeNumber, FirstName, LastName, Title, and BillingRate
    
   
9. 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 FROM Employees;

10. To see the result, right-click the title bar and click Datasheet View
11. Right-click the title bar and click Close
12. When asked whether you want to save, read the whole message box and click Yes
13. Notice that the Record Source is filled with a SELECT statement.
    Design the report as you see fit
14. Print Preview the report
15. Save and close the report

MCAS: Using Microsoft Office Access 2007 Topics

M1	Create queries

 

Exercises   

Yugo National Bank

1. Open the Yugo National1 Bank
2. Use the Simple Query Wizard to create a query based on the Customers table and that includes the AccountNumber and CustomerName fields. Save the query as Customers Accounts
3. 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

Watts A Loan

1. Open the Watts A Loan1 database
2. 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

World Statistics

1. Open the World Statistics1 database
2. 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
3. Using the Design View of the Countries Identities query to add the Independence and the NationalHoliday fields
4. Save and close the query

US Senate

1. Open the US Senate1 database 
2. Use the Design View to create a query based on the Senators table and populate it with SenatorName, Party, State, and WebSite
3. Save the query as Senators Identifications and close it