Lesson 1

Queries are often used to pull together data from multiple related tables. If you want a prettier presentation, you might then use the query as the basis for a report or form. However, queries are more powerful than just pulling data together, you can also use them to find the data you need and to perform calculations. This module discusses how to

1. Create a query with the Simple Query Wizard
2. Group and summarize data in a query by using the Simple Query Wizard
3. Identify the commonly used features of Query Design view
4. Create a query in Query Design view
5. View the results of the query
6. Save the query design
7. Use one query as the basis for another
8. Edit a query
9. Add criteria to queries
10. Understand how to use multiple criteria in queries to get the results you want
11. Specify the sort order for a query
12. Create a new field by using a calculation

Queries are flexible. They allow you to look at your data in virtually any way you can imagine. Most database systems are continually evolving and changing over time. Quite often, the original purpose of a database is very different from its current use. Here is just a sampling of what you can do with Access queries:

1. Choose tables: You can obtain information from a single table or from many tables that are related by some common data. Suppose you are interested in seeing the customer name along with the items purchased by each type of customer. When using several tables, Access combines the data as a single recordset (a set of records that meet given criterion).
2. Choose fields: Specify which fi elds from each table you want to see in the recordset. For example, you can select the customer name, zip code, sales date, and invoice number from tblCustomers and tblSales.
3. Provide criteria: Record selection is based on selection criteria. For example, you might want to see records for only a certain category of products.
4. Sort records: You might want to sort records in a specific order. For example, you might need to see customer contacts sorted by last name and first name.
5. Perform calculations: Use queries to perform calculations such as averages, totals, or counts of data in records.
6. Create tables: Create a brand-new table based on data returned by a query.
7. Display query data on forms and reports: The recordset you create from a query might have just the right fi elds and data needed for a report or form. Basing a report or form on a query means that, every time you print the report or open the form, you see the most current information contained in the tables.
8. Use a query as a source of data for other queries (subquery). You can create queries that are based on records returned by another query. This is very useful for performing ad hoc queries, where you might repeatedly make small changes to the criteria. In this case, the second query filters the first query’s results.
9. Make changes to data in tables: Action queries can modify multiple rows in the underlying tables as a single operation. Action queries are frequently used to maintain data, such as updating values in specific fields, appending new data, or deleting obsolete information.

Because Access web app tables are now tables in SQL Server, it should come as no surprise to you that, when you create queries in a web app, Access will create the SQL objects needed to support the query. Normally, simple Access queries are saved as SQL Server views while Access parameterized queries become SQL Server inline table-valued functions.

When you work only via Access 2013 web apps, you do not have to worry about the particulars of how Access 2013 manages the SQL Server object counterparts of queries. However, in scenarios where you connect to the SQL Server database containing your web app's data from another interface, such as a client database, it may be useful to take note of those particulars.
First, although an Access client query allows you to define sorting as part of the query, this is not normally a part of a SQL Server view.