This article is the last of a series on how to retrieve and update information stored in databases. It is organized into the following references:
Queries are used to retrieve a specific set of data from a record source. We generally use queries to be able to view, change, and analyze data in different ways.
Basically, when using ADO methods and properties, you may need to create queries for the following:
- to specify only the information you need to work on
- to organize information
- to answer a quick question.
Whatever your aim, the basic process of creating a query is the same. After you have decided what information you need to find, and what table or view it is stored in, you go thru the following steps:
- Choose which fields you want in the query results.
- Set selection criteria to find the records that will give the results you want.
- Set ordering or grouping options to organize the query results.
In addition, you can perform calculations on groups of records, such as to sum, average, count, and do other types of calculations for one or more chosen fields.
Queries can also be used to update a database ( add, edit or delete records ) or create or modify a database structure.
Queries are created using Structured Query Language ( SQL ). This article explores the essentials of the language, but first, let's look at the different types of queries in brief.
The most common type of query is a Select query that returns rows into a result set from one or more tables.
A Select query can contain specifications for the columns to return, the rows to select, the order to put the rows in, and how to group or summarize information.
A Select query does not change data in a table.
A parameter query is a query that when run prompts the user for information, such as criteria for retrieving records or a value to insert in a field.
Basically, providing a custom form that prompts for a query's parameters does this.
We can design the query to accept more than one parameter; for example, to accept two dates, and retrieve all records that fall between those two dates.
Parameter queries are best used when the query constraint is initially unknown.
In previous articles, I have shown several examples of parameter queries, such as the sample at right wherein you can type any portion of a string to search for.
This article focuses on select and parameter queries, though you can also use SQL to create the following query types, which we shall cover in later articles.
An action query is a query that can make changes to multiple records in one operation. Soon we shall look at, and provide examples of, four types of action queries: append, update, delete, and make-table.
An extended SQL query is a query for use in SQL-specific functions. Soon we shall look at, and provide examples of four SQL-specific queries: union, pass-through, data-definition, and subquery.
