Topics Search

Crosstab query with dynamic headings

Crosstab query with dynamic headings
Views: 54
I have a table I need to run reports on, so I need to run a cross tab query against it. I get to this far:

TRANSFORM Sum(dbo_PSALES.PeriodSales) AS SumOfPeriodSales
PIVOT dbo_PSALES.PeriodNo In (26,25,24,23,22,21);

Where the query runs fine, but as you can see I hardcoded the period numbers into the query. How do I turn it into a dynamic one? so everytime new period adds into the database query result will change also
Sponsored Links:

Similar posts...


Report from a Crosstab query column heading

Report from a Crosstab query column heading Icon
My report is not working because of the crosstab query column headings. The crosstab query pulls the last three months of data. It labels the column headings as the date. When I update the crosstab query and try to run the report it is looking for column headings that are no longer there (it drops a month each month I run the report).

How can I get the report to updateautomatically to only include the column headings available in the crosstab query instead of looking for the original ones that the report was designed on?

Setting crosstab headings dynamically

Setting crosstab headings dynamically Icon
I have a crosstab based on a query. Depending on the selection criteria used in the base query the crosstab will generate different column headings, predictable in advance. For example if the user chooses 'Fruit' the crosstab column headings will be 'orange, apple, pear' and if he chooses 'Vegetable' they will be 'onion, potato, carrot'. Can I set these column headings dynamically with VBA so that I can use just one crosstab for all user selections? Creating a different crosstab for each possible user selection and hard-coding the column headings in the crosstab properties isn't a good idea as the fruits might not always be 'orange, apple, pear'.

Assigning Nulls to a Crosstab Query

Assigning Nulls to a Crosstab Query Icon
I have a question about how to assign nulls within a crosstab query.

Like any other Crosstab query, my CT query generates column headings, row headings and associated data.

I use parameters on a form to generate this CT query. With wide criteria, all column headings get generated. However, with narrow criteria (narrow data range), certain columns/row headings get skipped.

In the final output, I need all rows/columns headings that I get with wide criteria to be present. I use these column headings as fields’ names in a query/macro that help in the generation of my final output (report).

With narrow criteria, I get the error message:
"The Microsoft Jet database engine does not recognize'[tablename.fieldname]' as a valid field name or expression"

My question is how do I force the crosstab query to generate column headings, even if no data is found for them. I would like to assign nulls/zeros to them.

Make summary data easier to read by using a crosstab query

Make summary data easier to read by using a crosstab query Icon
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.
In this article:
* Overview
* Create a crosstab query
* Use ranges or intervals for headings
* Prompt for a parameter to limit row headings
* Replace null values with zeroes
* Crosstab query tips

Crosstab column data count

Crosstab column data count Icon
I have a crosstab query with Well Name as row heading , site visit Dates as column headings (formatted as "y"), and averaged water level measurements data as the values. The column headings range from 2004-2010 and some wells have no measurements during some of these years.

I need to create a dataset from the crosstab that shows total number of years for which each well has data. My attempts at changing the column headings and values around to accomplish this have been futile.

crosstab report colum headings

crosstab report colum headings Icon
I have a crosstab report based on Duane Hookom's crosstab database. Now, I have a lot of data to report and I am trying to condense it down to as few pages as possible. I can get the data (detail) to be 10 columns wide in the report and still be readable. However, the last two column headings wrap into the next row even though there is plenty (at least it appears that way) of width room left on the page.

I have tried various things, but really do not know how to make it not wrap. I am fairly sure that it has something to do with the subreport I use for the column headings but . . .

Please help a frustrated hacker who has no more hair to pull out!

Crosstab Query and display all heading rows event empty value

Crosstab Query and display all heading rows event empty value Icon
I have created a crosstab query in Access 2007 and would like all the row headings to show regardless of whether there is a value.

Crosstab Criteria or Parameters

Crosstab Criteria or Parameters Icon
(Access 2007) I have created a crosstab query with times for row headings and names in the column headings. I am trying to set the criteria to pull information for the value from a specific date. It will not allow me the set a criteria at all and when I set the parameters, it asks for the date, but still lists all values from the query and not just that of the specified date. Any ideas of what I am missing?


TRANSFORM Statement Icon
Creates a crosstab query. When you summarize data using a crosstab query, you select values from specified fields or expressions as column headings so you can view data in a more compact format than with a select query.

Creating a crosstab query

Creating a crosstab query Icon
I need to create some crosstab queries and am unsure if I would be better off creating a new table from a query to hold the data for the crosstab query or is it okay to run a query and then use a crosstab query on that first query?

The crosstab query appears to work fine when using the initial query as a source but can someone advise me if that is okay to do?