3 Crosstab Query Techniques in MS Access

This article addresses some simple crosstab query techniques which can help while working in MS Access.

In a nutshell, the Crosstab query can be defined as a matrix, in which column headings are derived from the field values. It is the transformation of data rows into columns. It involves data aggregation like total sum broken down between months or products etc., in which these months are represented through columns. Today we are going to talk about some of Crosstab Query techniques which can help you work efficiently on MS Access.

3 Crosstab Query Techniques that would come handy while working on MS Access are:

Crosstab Query for displaying row Totals

In order to display the total calculation in a row comprising all columns, the user just needs to add a value field as Row Heading.

Example: In a database of a store, if they need to total their sales, they will have to add another field with Sales as Row Heading, which will display the total sales.

Users can also display the total below each of the columns by inserting the button in a ribbon. This button is located on the Home tab; you can locate it with icon (Σ) upper case representation of sigma.

Display Zeros instead of Blanks

Wherever the user doesn’t insert a value, there the columns are left as blank by default. Users can use Nz() command if they wish to display zeros instead. However, since Access is likely to misunderstand expressions, users should typecast the output or result with it. So, utilize CCur() for dealing with Currencies, CLng() for longer numbers (the whole number) etc.

Just simply Type in Nz() directly in TRANSFORM clause and all the blanks will be replaced by zero.

Specify Column Headings

Note that Column heading is obtained from a given field, and users only get to choose the fields which are relevant to their data.  So, if the user wants to limit their query criteria to sales made to a particular customer who hasn’t purchased anything during that time, then their field won’t be displayed at all. If the user wants to create a report using crosstab, it will present errors as the field name of that customer will just disappear.

For solving this problem, the user needs to enter every valid column heading in crosstab query’s Column headings property.

1. Go to Query Design screen, and select the option for Properties box from the Menu View.
2. Now locate and select “Column Heading property”. (In case you are unable to find it then you have opened field’s properties instead of Query properties).
3. Now insert all possible values in the space, which should be separated effectively by commas. Users can also delimit these values by using date values using # or with quotes.

Column Heading Disadvantages

• Any values which are not inserted by the user will be automatically remove from the given query.
• The fields can only show according to the order which is specified by the users e.g. “January”, “February”, “March”, …

Users can increase the speed of report design enormously by mentioning the relevant column headings wherever the report consists of a complex crosstab query like the Record Source. If the user does not specify its column headings, the Microsoft Access software will be unable to know which fields should be made available to reports without executing the complete query.

If you are regular MS Access user, you might have encountered a corrupted mdb or accdb file. While at times the default recovery feature in Access can extract your data, there would be cases where you would need additional help. For completely recovering Access databases, invest in a professional tool like DataNumen Access Repair.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdf fix and excel recovery software products. For more information visit www.datanumen.com