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.

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.

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.

Crosstab Excel Refresh

Crosstab Excel Refresh Icon
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?

Cross-tab query parameters:

Cross-tab query parameters: Icon
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 querie on Report

Cross-tab querie on Report Icon
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

apply filters on a cross tab quiery froma combo box in a form Icon
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