11 1 Queries Query One of the objects

11. 1 Queries • Query – One of the objects in Microsoft Access – It can help users extract data, which meets the criteria defined by them, from a database file. – It must be written in a specific format. – Microsoft Access provides different tools to help users compose a query. – After a query is submitted, Microsoft Access looks up the database and displays the requested data. The idea of a query

11. 1 Queries – Although a filter can help us find records that meet certain criteria, we can only obtain one set of filtered records from a table at a time. – The result cannot be further processed. – If we want to store and process the results after execution, queries should be used. The results of filtering cannot be stored for further processing.

11. 1 Queries – Although a filter can help us find records that meet certain criteria, we can only obtain one set of filtered records from a table at a time. – The result cannot be further processed. – If we want to store and process the results after execution, queries should be used. The results of filtering cannot be stored for further processing.

11. 2 Creating Queries – Ways to create a query in Microsoft Access: l Using Design View l Simple Query Wizard l Crosstab Query Wizard

11. 2 Creating Queries • Using Design View to Create Queries – In the Design View, users can define queries with selection criteria and sorting method to retrieve and arrange the required data from more than one table. Extracting data with a query

11. 2 Creating Queries • Criteria of a Query – In a query, criteria can be treated as the rule of retrieving data from a table. – The following table shows the usage of wildcard * and some logical operators which can be used for setting criteria in a query.

11. 2 Creating Queries • Criteria of a Query Symbol Description Example * Wildcard like ‘*D’ like ‘A*’ = Exact match = 100 = ‘Hong Kong’ > Greater than >0 >’A’ >= Greater than or equal to >= 1/1/2007 < Less than < 100 <= Less than or equal to <= 100 <> Not equal to <>’A’ Wildcard and logical operators used for setting criteria in a query

11. 2 Creating Queries • Creating Queries with Simple Query Wizard – The Simple Query Wizard is another tool for creating queries in Microsoft Access. – In the process of generating a query, the Simple Query Wizard prompts users with a series of questions and then generates the required queries based on the responses of users. – The Simple Query Wizard does not allow users to define their own criteria.

11. 2 Creating Queries • Creating Queries with Simple Query Wizard – The generated queries can be further modified in the Design View. – The Simple Query Wizard can also create a summary query. – A summary query is useful when we want to obtain statistical information such as the total sales amount and the average salary.

11. 2 Creating Queries • Creating Queries with Simple Query Wizard – The generated queries can be further modified in the Design View. – The Simple Query Wizard can also create a summary query. – A summary query is useful when we want to obtain statistical information such as the total sales amount and the average salary.