SQL question - Cross Tab query

I have a Cross Tab query that works fine. The SQL for this query ends with.

PIVOT tblData.Region

I've noticed that the column headings in my query result always show the Regions in alpha order (eg: East, MidWest, North1, North2, SouthEast). I've also noticed that I can 'force' the column order by using something like.

PIVOT tblData.Region IN(North1, North2, MidWest, SouthEast, East)

But, this later method presupposes that I know all of the Regions in advance. it won't be in the 'IN' function.

Here is my question - what techniques are available to manipulate the order of columns for the pivotfield? (for example, suppose I want to show all the values (unspecified) in the pivotfield in DESC alpha order. Or, assuming the Regions all have an autonumber RegID in tblReg, how can I get the column headings to display in this order?)

Sponsored Links:

Related Topics

crosstab query with datea parameters
I am trying to put a Date Parameter in a Cross Tab Query. When I use the initial queries (which the cross tabs is based off of) the wording below in the criteria works well to prompt date entry. However, when I try to run the cross tab query it states | (The Microsoft Access Search Engine does not recognize.) I need the user to be able to change the dates each time the cross tab runs.


Write Cross Tab Query in SQL view without using Transform.
I am using below query in Access database to create cross tab query.
.....
I want to use the same string in SQl but Transform is not in SQL so how can I use the above string in SQL without transform.


Cross Tab Query.
I want to retrieve the data for report through Cross Tab Query where I am facing problem with two issues:

1- How to fix Multi data field in Cross tab Query along other normal fields.
Like Products A,B then A,C,D so having multi products to select for selection.

- Is there any solution for the PM Key Field hide and display the name field.

like have ItemID, ItemName

so in Cross Tab it is showing the ItemID where as interested to see ItemName.


Cross Tab Query.
I want to retrieve the data for report through Cross Tab Query where I am facing problem with two issues:

1- How to fix Multi data field in Cross tab Query along other normal fields. Like Products A,B then A,C,D so having multi products to select for selection.

- Is there any solution for the PM Key Field hide and display the name field.

like have ItemID, ItemName

so in Cross Tab it is showing the ItemID where as interested to see ItemName.


Report for updated cross tab query in ms access 2007
I have cross tab query that shows the last closing balance of Inventories stored in various stores. Now I have 5 stores those store inventories. But stores may add later depending on quantity of inventories.

Now, I like to create a report that will show the cross tab query data. but the problem is when I create a report for that cross tab query it adds the stores as column those exists in query. but how can I add the future stores those will add in future.


Cross-tab query
I built a cross-tab query which contains 5 column-etiquettes ("explanation1" to 5)(and values and rows aswell of course). The columnt etiquettes comes from a table where I have month data registered by users.

Every new month the table data is deleted. This leads to that the cross-tab stops working as the etiquettes are gone. This means my report which is based on the cross-tab collapses.

I built a work-around - the delete query that delete before new import does not delete rows with date YY-MM-DD. This works, but it is a bit ugly.


Export Cross tab Query results to excel
I am trying to refresh an Excel spread sheet with the updated results of a cross tab query. I need to send the data every day to a certain tab within the spreadsheet. I can do the macro to export to excel, but I need it to go to the 2nd tab. It keeps wanting to go to the first tab which is my working part containing all the formulas for analysis. The second tab is for the data.

I tried using Microsoft query from within Excel to pull the data but that only goes to the tables whereas I need Access query results.


Cross Tab Query
Is there a way to create a cross tab query with column headings but without having to do a summary of any field? I do not want to total or summarize any of my row information


Cross Tab on Select Query
Can you base a cross-tab query on a select query that requires a parameter?

I ask this because I have tried but so far it has failed; raising an error related to the [forms]![formname]![field] statement used in the select query.

I know the select query does work and does what I need it to so the error is not there.


Displaying row heading on right side of cross tab query
cross tabs and I have an issue while creating a cross tab report.

I want to generate a cross table report like this

Username Jan Feb Mar TotalHrs
abc 34 23 15 72
xyz 28 31 23 82
def 23 33 19 75

I want the field TotalHrs to have the sum of months (Jan,Feb and Mar)

I defined TotalHrs as rowheading in cross tab and I am getting the expected output but I am getting the report like this

Username TotalHrs Jan Feb Mar
abc 72 34 23 15
xyz 82 28 31 23
def 75 23 33 19

Can't I display the TotalHrs field on the extreme right end


Cross tab query - group hours
I have a table with

ID
Time (hh:mm)
Hospital

Is there a way I can set up a cross tab query that counts the number of episodes for each hospital between the hours of


Reports written from cross tab queries
I created three separate cross tab queries to pull data from, however I want to write one report with a parameter that will allow the report to be ran by date range without having to input the criteria in each query.

Can this be done


Printing crosstab query
I would like to print a cross tab query directly.I need to change the date and title of it.but don't know How.since my cross tab query is not monthly and my columns are changing,I can't make a report out of it, that's why I wanna print it directly.


Cross-tab querie on Report
I have a cross-tab query which I want to put in a report. I cannot find a way to get this done.


apply filters on a cross tab quiery froma combo box in a form
I have a CrossTab Query,(Daily Sales Plan), I want filter only the model selected in a combo box in a form, I'm using this criteria : nz( Forms![Frm1_GeneralQuery]![ComboModel],"*") so, it would filter only the model selected in combobox and filter on my cross tab, but it not even let me save changes on the cross tab, a messaga comes out saying that access does not recognize this data as a valid name or criteria


Cross-tab query parameters:
I have a report that is being populated by data in a cross-tab query. Because I need to have the user specify the date range for the report, I have a criterion in the query that reads "Between [Enter Start Date] And [Enter End Date]".

This has worked fine in all my other queries and reports, but I couldn't make it work for the cross-tab query. Then I read about definingthe parameters to make that kind of operation work.

So I went in and defined [Enter Start Date] as Date/Time and [Enter End Date] as Date/Time. Fine.

But now, whenever I enter the date range, I get the "Enter Parameter Value" message box three or four times for both the start date and end date inputs. Sometimes it will even pop up while I have the report in Design mode. No reason why -- it just seems to pop up.

I've even tried building a second query just to limit the date range and have that one feed the cross-tab, but no luck; it still does the same thing. Is there some more straightforward way of tackling this?


Cross-tab query YTD
This is my first time visiting this forum.

I've come to you with a question that has been stumping me for awhile. I've searched and searched on this topic to no avail. My CEO is asking for certain information over the past five years, but in each year, he only wants the YTD information, not that entire year's information. He wants to compare the current YTD to past years.

I've tried to use the DateSerial function, but it doesn't seem to work well in a cross-tab query. So for example, I want to run a report based on invoice dates through July 31st of each of the past five years


Crosstab Excel Refresh
I currently have a cross tab query that I have linked to excel. Each week new data will add a new column to the cross tab, but when I refresh the query in excel these new columns do not refresh into the sheet as they were not part of the query the week before. I guess this is a common problem?


Crosstab with totals on the right
I have a cross-tab which I need to get all the totals on the right side

As of now I am only getting the totals on the button, on the columns I need to see totals by row

Here is a picture of my Cross-tab query


Splitting with Delimiter and populating a Cross Tab Query
Ive been trying to convert some data that come out of a SharePoint Survey, its not the nicest and looks like this:

;#Answer 1;#Answer 2;#Answer 3;#

I would like a cross tab query that has a row header for each value between ";#" and then count them

e.g. if there are 2 lines of date like this:

;#Answer 1;#Answer 2;#Answer 3;#
;#Answer 1;#Answer 3;#

It would look like:

Answer 1 Answer 2 Answer 3

Ive been trying to use the Split function but couldnt figure it out