• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint

6.3. Access Query Types

Access supports a variety of query types. Here is a list, along with a brief description of each:

  • Select Query. These queries return data from one or more tables and display the results in a result table. The table is (usually) updatable, which means that we can change the data in the table and the changes will be reflected in the underlying tables. Select queries can also be used to group rows and calculate sums, counts, averages, and other types of totals for these groups.

  • Action Queries. These are queries that take some form of action. The action queries are:

    • Make-Table Query. A query that is designed to create a new table with data from existing tables.

    • Delete Query. A query that is used to delete rows from a given table or tables.

    • Append Query. A query that is used to append additional rows to the bottom of an existing table.

    • Update Query. A query that is used to make changes to one or more rows in a table.

  • SQL Queries. These are queries that must be entered in SQL View. The SQL queries are:

    • Union Query. A query that creates the union of two or more tables.

    • Pass-Through Query. A query that passes the uninterpreted SQL statement through to an external database server. (We will not discuss these queries in this book.)

    • Data-Definition Query. These are queries that use the DDL component of SQL, such as CREATE TABLE or CREATE INDEX.

  • Crosstab Query. This is a special type of select query that displays values in a spreadsheet format, with both row and column headings. For instance, we might wish to know how many books are published by each publisher at each price. This is most conveniently pictured as a crosstab query, as shown in Table 6.1.

    Table 6.1. A CROSSTAB Query
    Price Total Big House Medium House Small House
    $12.00 1   1  
    $13.00 3 2 1  
    $15.00 1 1   
    $18.00 1 1   
    $20.00 6   1 5
    $25.00 2 2   
    $34.00 5 1 4  
    $44.00 1   1  
    $49.00 6 1 4 1
    $99.00 1   1  

  • Parameter Query. For select or crosstab queries, we may choose to let the user supply certain data at run-time, by filling in a dialog box. This can be done in both Design View and SQL View. When the query asks for information from the user, it is referred to as a parameterized query, or parameter query.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint