Microsoft Office Tutorials and References
In Depth Information
Figure 5-22
Completed crosstab query design
selected field
for crosstab
calculation
Sum aggregate
function selected
option to display
an overall tot als
column in the
crosstab query
InvoicePaid field
values in columns
City field
values in rows
7. Click the Next button to open the final Crosstab Query Wizard dialog box, in which
you choose the query name.
8. Click in the text box, delete the underscore character so that the query name is
qryCustomersAndInvoicesCrosstab, be sure the option button for viewing the
query is selected, and then click the Finish button. Access saves the crosstab
query, and then displays the query recordset.
9. Resize all the columns in the query recordset to their best fit, and then click the
City field value in the first row ( Battle Creek ). See Figure 5-23.
Figure 5-23
Crosstab query recordset
paid invoices by city
unpaid invoices by city
The query recordset contains one row for each City field value. The Total Of
InvoiceAmt column shows the total invoice amount for the customers in each city. The
columns labeled -1 and 0 show the total paid (-1 column) and unpaid (0 column)
invoice amounts for customers in each city. Because the InvoicePaid field is a Yes/No
field, by default, Access displays field values in datasheets, forms, and reports in a check
box (either checked or unchecked), but stores a checked value in the database as a -1
and an unchecked value as a zero. Instead of displaying check boxes, the crosstab query
displays the stored values as column headings.
 
Search JabSto ::




Custom Search