(c) Summary Queries : A summary query, as opposed to a simple query, is used to extract aggregate of data items for a group of records rather than a detailed set of records. This query type is of particular importance in accounting because the accounting reports are based on summarisation of transaction data. Consider the following SQL statement : SELECT Code, Name, Sum(Amount) From Vouchers INNER JOIN Accounts ON ( Accounts.Code=Vouchers.Debit) GROUP BY Code , Name In the above query, the Vouchers table has been joined with Accounts table on the basis of Code field of Accounts and Debit field of Vouchers. The resultant record set has been grouped on the basis of Code and name of accounts.
Accordingly, the sum of amount for each group (or set of records) has been ascertained and displayed. Finding the sum is the process of summarisation. . .
Adding Computed fields The computed fields, representing secondary data, do not form part of data stored in tables because such data items unnecessarily increase the size of database. The secondary data items can always be generated on the basis of primary (or stored) data. In order to find values of such secondary data items, the query is based on computed fields. The computed fields provide up-to- date calculated results because they rely upon updated stored data values.
For example, a data table, named Sales , which includes ItemCode, Quantity, Price, Dated and CustId, is maintained in a database to store sales transactions. In order to get list of sales transactions along with total sales relating to CustId=’A051', the following simple query is executed by including Sales as computed field : SELECT Dated, ItemCode, Quantity*Price AS Sales FROM Sales WHERE CustId= ‘A051’ ; In the above query the expression Quantity*Price has been given the name Sales by using AS clause. . .
Using Functions in Queries A function in the Access environment is named and followed by parenthesis ( ). The function receives some inputs as its arguments and returns a value (also called its output). These functions also form a part of the expression for a computed