Crosstab queries are really powerful. Like so many things in Access, there's a lot to learn to use them effectively. This article from Microsoft would be a great place to start if you were new to crosstabs.
I guess if I were new to crosstabs I might not know why I needed one. Here's an excerpt from the article.
When you want to restructure summary data to make it easier to read and understand, consider using a crosstab query.
A crosstab query calculates a sum, average, or other aggregate function (aggregate function: A function, such as Sum, Count, Avg, or Var, that you use to calculate totals.), and then groups the results by two sets of values— one down the side of the datasheet and the other across the top.
Think a row per sales person and a column per month with total commission per person per month. Or average salary by pay band by division, with pay bands across the top and divisions down the left. This is the functionality Excel calls pivot tables.
Actually despite starting at the beginning, the article seems to cover most of the things you could need to know about crosstab queryies, including specifying the column headings you want, filtering, and handling nulls. The only thing it doesn't cover is reports based on crosstab queries, which get messy because the queries columns can vary with the data. I'll have to watch for a good article on that.
No comments:
Post a Comment