Microsoft Office Tutorials and References
In Depth Information
Comparing a select query to a crosstab query (continued)
Figure 5-20
paid invoices
unpaid invoices
one row for Lansing
invoice amounts
The qryCustomersAndInvoices query, a select query, joins the tblCustomer, tblContract,
and tblInvoice tables to display selected data from those tables for all invoices. The
qryCitiesAndInvoicesCrosstab query, a crosstab query, uses the qryCustomersAndInvoices
query as its source query and displays one row for each unique City field value. The City
column in the crosstab query identifies each row. The crosstab query uses the Sum aggre-
gate function on the InvoiceAmt field to produce the displayed values in the Paid and
Unpaid columns for each City row. An entry in the Total Of InvoiceAmt column repre-
sents the total of the Paid and Unpaid values for the City field value in that row.
Select Queries versus Crosstab Queries
|
InSight
A select query displays several records—one for each row selected by the select
query—while a crosstab query displays only one summarized record for each unique field
value. If you want to analyze the records in a query to see the big picture, you would start
with a crosstab query, identify which field values to analyze further, and then look in detail
at the select query for specific field values. Both select and crosstab queries serve as valu-
able tools in tracking and analyzing a company’s business, and you should use each type of
query in the appropriate situation.
When you create a query in Design view or with a wizard, Access automatically constructs an
equivalent SQL statement and saves only the SQL statement version of the query. SQL
(Structured Query Language) is a standard language used in querying, updating, and man-
aging relational databases. If you learn SQL for one relational DBMS, it’s a relatively easy
task to begin using SQL for other relational DBMSs. However, differences exist between
DBMSs in their versions of SQL, somewhat like having different dialects in English, and in
what additions they make to SQL. The SQL statement equivalent that Access creates for a
crosstab query is one such SQL-language addition. If you need to convert an Access data-
base to SQL-Server, Oracle, or another DBMS, you should know that your crosstab queries
most likely will not work in these other databases.
 
Search JabSto ::




Custom Search