Crosstab query with dynamic headings

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
SELECT dbo_PSALES.UniqueID
FROM dbo_PSALES
GROUP BY dbo_PSALES.UniqueID
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:

Related Topics

Report from a Crosstab query column heading
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
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
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
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
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
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
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
(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
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 dynamic crosstab report
I have a report whose record source is a crosstab query. This report is displaying training done by Territory and I would have added labels and controls for all of the Territories. I haveto submit monthly reports and in a particular month there may be several Territories which have not done any training.

When I try to preview the report, I get errors because the crosstab query does not contain the Territories where no training was done.

Is there a way to create a report and have the labels and control "changed" to match the Territories in the crosstab query? The Territory is the column heading in the crosstab query.


Creating a crosstab query
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?


Crosstab not accepted as subform
I have a set of data I want to display as a matrix.

Along the top are three column headings (P, M, D).
In the first column are the row references, which are numbers 1 to 10.
Where these row and column references cross is a lot of text.

Basically I can then identify P1, M3, D5 etc (battleships in a way).

I have created this as a crosstab query with no issues.

The key field for this query is set as unit_id (NOT used in the actual crosstab table but I can set this to, say, 44 and the table shows the results for course 44).

I have a form that lists all the courses and has the key field unit_id.

If I try to embed the query into the form the system proposes connecting unit_id to unit_id but then says it is not in the query. If I set to define my own I find that, sure enough, the unit_id query is not showing - though it IS in the query.

What can I do to connect the form to the crosstab query using the key field unit_id


Crosstab Query Parameter
Access 2007,

I have a crosstab query that contains a field called AccountName in one of my row headings.

I have a form with a list box containing all of the account names. What I would like to do is select an accountname from the list box then have the crosstab query use that accountname selection when it runs.

I was using something like [FORMS]![frm_Main]![Acct List] for the criteria but the error I am getting is ".the database engine does not recognize [FORMS]![frm_Main]![Acct List] as a field name or expression.


Crosstab Query Parameter
access 2007,

I have a crosstab query that contains a field called AccountName in one of my row headings.

I have a form with a list box containing all of the account names. What I would like to do is select an accountname from the list box then have the crosstab query use that accountname selection when it runs.

I was using something like [FORMS]![frm_Main]![Acct List] for the criteria but the error I am getting is ".the database engine does not recognize [FORMS]![frm_Main]![Acct List] as a field name or expression


Sum of Data in each column
Created a crosstab query that has months of the year as rows and budget categories as columns. My column headings are now showing the budget category ID #'s. (1, 2, 3 ,4) I want them to show a different field from that table -- i.e. Budget category # (2221, 2252, etc) or I could use the category description (travel, per diem, etc). Either way will work - anything but the single digit ID #'s. Prefer simple query with expression over crosstab.

NOW: (as crosstab)
August
Sept
OCt

PREFERRED: (simple query, using expressions maybe - more flexible ?)

2252-Mileage 2251-Per Diem 2253-Food


Crosstab query
I am trying to perform a crosstab query and trying to figure out what to put in the row and column and value headings.

I am trying to find the average training hours per employee for 7-2009 to Present

So my fields are:
Agency UID-Need to know total employee
Contact Hours-Total contact hours and divide them


Crosstab queries
A crosstab query is used when we want to represent a summary query with two grouping columns like a double input table, in which each one of the group columns is an entry, one down the left side of the datasheet and the other across the top. Learn the crosstab Query wizard and the design view of a crosstab query.


Column Headings For Crosstab Query
I am passing a sql statement for a crosstab query, as such, in VB Code:
___________
strSQL = "TRANSFORM.
"SELECT .
"FROM .
"WHERE.
"GROUP BY .
"PIVOT myField IN ('Apple', 'Orange', 'Banana')

CurrentDb.QueryDefs("myQuery").SQL = strSQL
____________

If I look at the ColumnHeading property in design, it's set to 'Apple', 'Orange', 'Banana'

The query in Access however is sorting as such. Banana, Orange, Total, Apple

Why isn't my PIVOT IN line sorting the column headings in the order I want!?!?


Dynamic Crosstab Report
I have a crosstab query that looks like this:

SupervisorName EmployeeName NumberofCalls 17 18 19 Michael Jordan Scottie Pippen 3 5% 10%
Joe Montana Jerry Rice 7 6%

I need to create a report from the crosstab query that is dynamic. The numbers 17, 18, and 19 represent different questions. Such as, "Was the caller's name requested correctly?" The numbers below that say how many times the question has been missed, so Scottie has missed that question 5% of the time on his phone calls.

Here is the tricky part. Each person takes different types of calls like Sales, and Customer Service. Each type of call has a different amount of questions - Sales = 10 and Customer Service = 15.

For the report I need to have the actual question for the type of call specified show up instead of the number 17,18, 19, etc


Dynamic Multivalue Crosstab Query
I'm after a clever way of creating a cross tab query of data from a Source Table by specifying the column headings in a Column Table without using Iif. Can it be done?

Tables
1. Source Table
2. Column Table contains a list of Column Names that are in Source Table.

In a query, is there a way of achieving something like

Data:[SourceTable].[[ColumnTable].[Column Name]]

without using Iif, which presumes that you know what is in Column Table?

I know the Iif approach works but the nested Iif statement needs to match the entries in Column Table.