Crosstab Query Primer?

[Guest]

I ask forgiveness in advance for my ignorance. Can someone provide links to a
meaningful discussion/tutorial on crosstab queries? My specific issues:

What functionality do they provide?

Can the filter be set via a dialog box, so that one crosstab query can be
used with multiple sets of keyed data?

Can a crosstab query be setup to spin through all data in a table, and group
its totals only when the major-sort field-value changes?

The "How do I?" answers for these questions would be deeply appreciated.

Thanks!

 

[Allen Browne]

Crosstabs can be confusing. The basic idea is to turn rows into columns.

For example if you have a table set up like this:
SalesMonth Salesman Amount
Nov 2004 Freda $4000
Nov 2004 Joe $2000
Dec 2004 Freda $5000
Dec 2004 Joe $1000
then you can use a crosstab query to you can turn the Salesman field into
the column headings so you get this kind of output:
SalesMonth Freda Joe
Nov 2004 $4000 $2000
Dec 2004 $5000 $1000

A crosstab must have:
- at least one RowHeading - the field that should appear at the left
(SalesMonth above);
- exactly one ColumnHeading - the field that should supply the names for the
columns (Salesman above);
- exactly one Value - whatever you want to appear at the intersection
(Amount above)

You can use parameters with a crosstab query so you get the little dialog
popping up and asking for a value, but you must declare them. To do that,
choose Parameters on the Query menu, and enter the names there exactly as
they appear in the query.

You can (and usually do) group data in a crosstab query, grouping on one or
more fields. You can also restrict the crosstab to only certain values, by
choosing WHERE in the Total row under some fields, and entering criteria
below that.