3.2 Queries Object
In MS Access you can use queries to extract information (data) from one (or
more) tables (or from other queries). The information you retrieve can be
limited by the definition of your query. In access queries are database objects
that can be used to retrieve a subset of data. unlike filters queries can be
saved and used as a data source for other ms access objects such reports, forms
or data access pages. For example you can write a query that returns all
students that have a GPA that is less than 3.0 (so you can send them a worming
email). Queries are powerful tool because they can be used to retrieve
information from multiple tables by joining these tables using common fields (we
will cover join operation later in this section). Besides limiting the number of
records with queries, you can also limit the number of columns (fields). For
example instead of returning all fields from the Student table you can only
select the student ID, Student Name, Student GPA and Student Email address. In
summary queries are used to change, view, and analyze data. In Access there are
several types of queries, in the following section I will cover Select Queries,
Select Queries,
Parameter Queries,
Crosstab Queries, and
Action Queries.
MS Access 2003 Book
3.2.1 Select Queries and the Structured Query Language (SQL)
Select query can be used to 1) view subset of records 2)Retreive data
form one (or more) tables 3) Display the results in a datasheet 4) update the
records from within the datasheet 5) group records and do calculations (sum,
average, counts, totals, etc).
Queries are made of Structure Query Language (SQL) Commands. Structured Query
Language (SQL) can be used to query, update, and manage relational databases
such as MS Access, Oracle, SQL Server, MySQL, etc. In general a SQL
statement is an expression that defines an SQL command, (SELECT, UPDATE, or
DELETE) and includes clauses such as WHERE and FROM, or ORDER BY.
For example, the following SQL command retrieve students with GPA <3.0 from the
Student table:
SELECT StudentID, StudentName, StudentGPA, StudentEmail
FROM tblStudents
WHERE GPA < 3.0;
In the above command SELECT is an SQL keyword that means retrieve these
fields (we could use * to retrieve all fields). FROM clouse is another SQL
keyword that specifies the table name. The WHERE clause is a SQL keyword that
determines the selection criteira (the restriction we are placing on the records
to be returned) in our example all records with GPA < 3.0 will be returned.
The SQL Example below returns all records and all fields from the tblStudents
table:
SELECT *
FROM tblStudents;
The SQL example below uses the ORDER BY clause to sort the returned records
in Ascending order using the StudentName field:
SELECT *
FROM tblStudents
WHERE GPA < 3.0
ORDER BY tblStudents.StudentName
3.2.2 Creating Queries in MS Design View
When selecting the Query object you can double click on the Create Query in
Design View to open the query design grid. You need to select one or more tables
from the Show Table dialog box before you can start working with the design view
as shown in the figure below. Once tables are selected you can grap fields from
the tables field list and drop it into the grid area and that field will be part
of the query. You can also drag the wild Character * and drop it into the design
grid area to include all fields from that table into the query. In the Design
grid you can specify conditions in the criteria area, you can connect these
conditions using the or / and operators. For example you can select all students
with GPA <3.0 and Credit hours > 50. Here is the SQL command fo this example:
SELECT *
FROM tblStudents
WHERE GPA < 3.0 And TotalCreditHours >50;
When you create a query in query Design view as shown in the above figure,
Access builds the equivalent SQL statements behind the scenes for you. You can
see the command by switching to the SQL view from the view drop down list. Or
you can run the query and see the output in Datasheet view. Query properties in
the property sheet in query Design view (design grid above) have equivalent
clauses and options available in SQL view. When you create a query in Design
view, Access builds the SQL equivalent for you. The SQL statement constructed
can be edited and viewed using the SQL view.
3.2.3 Parameter Queries
A parameter query is a query that displays a dialog box prompting users
for information, that information can be used for retrieving records or a value
you want to insert in a field. You can design the query to prompt you for more
than one piece of information; for example, you can design a query to prompt a
user for two dates (beginning and end date). Access can then retrieve all
records that fall between those two dates. Parameter queries are also can be
used as the basis for forms, reports, and data access pages. For example, you
can create a monthly earnings report based on a parameter query. When you print
the report, Access displays a dialog box asking for the month that you want the
report to cover. You enter a month and Access prints the appropriate report.
3.2.4 Crosstab Queries
Crosstab queries calculate a sum, average, count, or other type of
total for data that is grouped by two types of information— one down the
left side of the datasheet and another across the top.
3.2.5 Action Queries
An action query is a query that makes updates to many records at the
same time. There are four types of action queries:
- Delete Queries A delete query deletes a
group of records from one or more tables. For example, you could use a
delete query to delete products that are with no orders. Delete queries
remove the entire record, not just selected fields within records.
- Update Queries Using update query you can
make global changes to a group of records in one or more tables. For
example, you can raise grades by 10 percent for all Students (or a subset of
students).
- Append Queries An append query adds a group
of records from one or more tables to the end of one or more tables. For
example, suppose that you drop Students form one Class1and register them for
Class2. You can append records into the Class2 and delete them using a
Delete query form Class1.
- Make-Table Queries A make-table query
creates a new table from data in one or more tables.
|
