Topics Search

SQL question - Cross Tab query

SQL question - Cross Tab query
Views: 1
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:

Similar posts...


crosstab query with datea parameters

crosstab query with datea parameters Icon
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.

Write Cross Tab Query in SQL view without using Transform. Icon
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.

Cross Tab Query. Icon
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

Report for updated cross tab query in ms access 2007 Icon
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

Cross-tab query Icon
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

Export Cross tab Query results to excel Icon
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

Cross Tab Query Icon
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

Cross Tab on Select Query Icon
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.

Cross tab query - group hours

Cross tab query -  group hours Icon
I have a table with

Time (hh:mm)

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

Printing crosstab query

Printing crosstab query Icon
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.