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:

More topics

 

crosstab query with datea parameters

Preview
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.

Preview
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.

Preview
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.

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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