Microsoft Access Queries

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.      


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.
MS Access 2003 Tutorial

Flexpack Services

| Home | Hosting | Domains | Support |  Contacts |
Terms & Condition | Privacy Policy |


Copyright 2005 by HostItWise.com Read our Copyright. All rights reserved.

RushRash Inc |