creaate a crosstab query in access

What is a crosstab query?

Crosstab queries unit data retrieval queries used primarily for coverage functions. They dissent from
typical select queries in Microsoft Access during this they will pivot collective row data into a columnar format.
The one demand to the current question is that the data ought to be collective in some fashion. The results from these queries unit somewhat reasonably like spreadsheets in their format. However, the numerous data is diagrammatic in columns, rather than rows.

The crosstab rundown

The idea of pivoting your information square measure typically somewhat confusing ab initio, but once you grasp variety of simple points, you'll be on your because of writing your own.


Crosstab queries would like slightly little of turning out with. that's to not say that each one queries don't would like some type of turning out with, but crosstab question vogue is somewhat utterly completely different, and you want to follow a number of rules once you turn out them:

• Crosstab queries square measure ceaselessly aggregate. The aggregations ought to be performed to produce define data based totally upon required groupings for the crosstab format.

•  after you perform a crosstab question, you would like to administer a minimum of three varieties of crosstab risk data. you would like to specify one or further Row Headings, one Column Heading, and one price risk.

Row Heading: This crosstab chance is painted vertically in your dataset. smart candidates for this grouping unit product kinds or various categories of knowledge you'd prefer to combination. you'll need multiple row heading columns, so multiple column aggregations unit allowed.

Column Heading: This crosstab risk is painted horizontally in your dataset. wise candidates for this grouping square measure sales quarters or different categories of data that you just would love only one grouping aggregation, as a results of only one column heading is allowed in associate degree extremely crosstab question.

Value: This crosstab risk is that the information that's typically summarized in your crosstab question. it's the merchandise of cross-referencing your Row Heading(s) and your Column Heading aggregation.

A crosstab example

For this instance, we're getting to look into may be a report relating to company sales transactions. First, we'll ought to produce a table to carry our sales. We'll decision it SalesTransaction. it is a comparatively easy table that contains the fundamental key components most sales tables have: a kind of product, the sale value of the merchandise, and therefore the date of the sales dealings. Figure A shows the look of the table in Microsoft Access. you'll transfer a sample info containing this table if you wish to follow along side this instance.

Figure A

I created dummy information for the SalesTransactions table so we are able to manufacture some queries from it. to make the information, I used what is called a intersection (Cross Join). The question named qryCreateSampleData within the sample info can introduce you to the current great tool for making dummy information for testing functions.

Now that we've a structure and table to question, we're able to produce our crosstab question. Open a brand new question window and add the SalesTransactions table to the question designer. Then, navigate to the question kind drop-down list and choose Crosstab question, as shown in Figure B. you will notice that once you choose this question  possibility, the aggregations ar instantly flipped on.

Figure B

Our goal is to gift to management a report particularization product sales by quarter. Of course, we tend to might gift this data through a typical choose query; but, the info would be additional|far more|rather more|way more} tough to soak up and would most likely need some massaging by management to gift the numbers in a very more pregnant manner. this can be what is nice regarding crosstab queries: they permit you to gift information in a very manner that is easier to scan and perceive.

Because we're getting to explore product sales by quarter, we want to combination information by our ProductType and our TransactionDate columns. however you show the information depends on your preference, however once i am running crosstab queries based mostly upon quarters, months, or years, I generally wish to gift date information as column headers, which is able to leave ME to show the merchandise information as a row heading.

Once we've set our Row Heading and Column Heading options, we need to decide which Value crosstab option to use. We'll use the ProductPrice field here because we want to see sales information, which means we'll need to SUM that data.

Figure C shows our question. Notice that we have a tendency to ar victimization 2 Row Heading crosstab choices. Adding the ProductPrice once more and summing it as a row heading can truly act as a outline field for the Sales Quarters figures.

Figure C

Figure D shows the output from our crosstab query. As you'll be able to see, we have a tendency to've accomplished what we taken off to try to to. The TotalSales column is that the summation of all four quarters of product sales for the particular ProductType.

Figure D

Although crosstab queries are not applicable for all things, they are value their weight in gold once applicable. they allow you to reference classified information and summarize those groupings in a very meaty method. Happy querying!