Share this Page URL

Chapter 9. Extending Your Use of Queries > Understanding the Basics of SQL - Pg. 309

Extending Your Use of Queries 309 A. You can select as many as three fields for row headings. Only one field can be designated as the column heading. Q.Can you update data in a crosstab query? A. No, the data a crosstab query displays can't be updated. Just the Steps Create a crosstab query 1. 2. 3. 4. 5. 6. In the Database window, click Queries in the Objects list, and then click New. In the New Query dialog box, select Design View, and then click OK. (You can also select Crosstab Query Wizard in the New Query dialog box and follow the wizard's instructions to create a crosstab query.) In the query design grid, use the Show Table dialog box to add the tables you need in the query. On the Query menu, click Crosstab Query. Add fields to the query design grid. In the Crosstab row, select Column Heading in the field to use for column headings, select Row Heading in the field or fields to use as row headings, and select Value in the field that contains the data you want to summarize. In the Total row for the value field, select the summary function you want to use ( Avg, Count, Sum, and so on). On the toolbar, click the Run button to see the query's results. 7. 8. Understanding the Basics of SQL In this section, we'll cover the fundamentals of SQL, a standardized database querying language that Access uses to define the queries you create in the query design grid. You can create a query (or a filter) in Access by writing an SQL statement, but the query design grid provides a user interface that many people find easier to use and allows you to create complex queries. Becoming familiar with the main SQL keywords will help you understand what the Access query engine is doing, and as you saw in Chapter 8, you'll often see SQL statements as the record source for a control on a form or a report. Learning SQL will also enable you to do more in Access. For example, some types of queries that we'll review later in this chapter require you to use SQL. Let's look at the SQL generated for the expense summary query we created in Chapter 6. View a query's SQL statements 1. 2. With the file HelloWorld9.mdb open, click Queries in the Database window, select qryExpen- seSummary, and then click Design. On the View menu, click SQL View. You'll see the SQL SELECT statement that defines the query, shown here. The statement is also listed below for ease of reference.