Types of Queries
Access enables you to create the following types of Queries
- Select - This type of query displays data from one or more tables, based on user - defined criteria.
- Find Duplicate - This type of query displays all records with duplicate values in a specific field. For example, you can find out details of customers who have bought more than one product.
- Find Unmatched - This type of query displays all records that do not have a matching record in another table. For example, you can find details of customers who have not bought any product.
- Parameter - This type of query, when executed, displays a dialog box in which you can enter the criterion for retrieving data. This enables you to customize the output of your query based on the value that you enter while executing it.
- Crosstab - This type of query calculates a sum or count and groups the results of the query. For example, you can display the items grouped first by Category and within the category by suppliers.
- Update - This type of query is used to make changes in the records in a table. For example, to change the price of a product from Rs.10 to Rs. 12.50.
- Append - This type of query adds records extracted from one table to another table.
- Delete - This type of query deletes records from tables, based on a specified criterion.
- Make - Table - This type of query creates a new table from a group of records extracted from other tables.
Creating Queries in Access
A complete view of data stored access various tables is achieved only by executing queries based on SQL. A query is capable of displaying data records contained in many data tables. There are many types of queries in Access that are used to generate reports which are given below-
- Simple Query - This type of query enables us to identify a field from the database. For example, to find amount of transactions record contained in voucher no. - 01, we can use the following SQL statement:
WHERE Voucher no - '01'
In the above SQL statement, the SELECT statement is meant to specify the field to be selected, FROM clause specifies the source of data, and WHERE clause filters the records matching the condition that Voucher No = '01'.
- Parameter Queries - This type of query is used to identify a set of records which contains the same criteria. Consider, to search voucher records of a particular account from the Accounts database, we can use the following SQL Query:
WHERE Code = Account No.
- Summary Queries - This query type is of particular importance in accounting because it enables us to create accounting reports based on summarization of transaction data.
FROM Vouchers INNER JOIN Accounts
ON (Accounts, Code = Vouchers.Debit)
GROUP BY Code, Name
Methods of Creating Queries
Access provides the following two methods of creating a query:
- Using Design View - The Design view enables you to create a query from scratch. It enables you to specify the tables, fields and criteria of a query.
- Using Query Wizard - The Query Wizard displays a series of dialog boxes in which you can specify the tables and fields that you want to include in the query.
- Click Queries in the Objects Bar in the Database window.
- Double click the Create Query in Design view option.
At this point, show Table Dialog box is displayed:
- Click Tables to opt for the name of the table that you want to add to the query.
- Click the Add button
- Repeat these steps till you add all the required tables.
- Click the close button.
At this point, show Table dialog box is closed and the Select Query Window is displayed.
- Click the name of the table from which you want to add a field to the query in the dropdown list box in the Table row.
- Click the name of the field from which you want to use in the query from the dropdown list box in the Field row
- Click the show option if you want to display the field in the output of the query.
- If there is a criteria for the field, type it in the Criteria Field.
- Type the name of the query in the Query Name box.
- Click OK
The query is saved.
SQL View Method to create a Query: It is also a method of creating Query in Access. This type of SQL Query statement is written by using the keyboard. While forming the SQL statement, the following clauses are normally used for generating queries:
- SELECT - This clause is used to specify the fields to display data which were given after SELECT in the SQL Statement. SELECT Vno., Name, Amount
- INNER - This clause is used to display matching records between two data tables. To make relation between two data tables, we have to type Join after the clause. For example, if we have to select those records of Debit Voucher and credit Voucher table whose category ID and TYPE respectively are the same, we can use the following SQL statement.
FROM Credit voucher INNER JOIN Debit Voucher
ON (CatId = Type)
- LEFT - This clause in a SQL query statement enables the display of matching records of those tables along with the records (matched and unmatched) given in the first table in the statement. Consider the following example:
FROM Credit Voucher LEFT JOIN Debit Voucher
ON (CatId = Type)
This statement will display all records of Credit Voucher along with the matching records of Debit Voucher.
- RIGHT - This clause is the opposite of the LEFT Clause and displays all records of Debit Voucher along with the matching records of Credit Vouchers, the statement is as given below:
FROM Credit Voucher RIGHT JOIN Debit Voucher
ON (CatId = Type)
- ORDER BY - By using this clause in SQL query statement, we get the result in the order in which the resultant records of the query are required to appear.
ORDER BY Type, Code
- GROUP BY - This clause is used in the SQL statement to provide grouping of records for creating a summary. For example,
SELECT Credit, Sum (Amount)
GROUP BY Credit
This SQL statement results in display of the total amount by which every transaction has been credited.