SQL question - Cross Tab query
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?)
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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.
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.
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.
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.
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.
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