I need help constructing a crosstab query within Access 2007.
My Access 2007 app has a SQL Server database as its back end. I retrieve the necessary data and place it into a temporary local table so the user can do some "what iffing" without having to constantly go back to SQL Server.
My Access temp table TmpTable has numerous fields, but the 3 key ones involved in this problem are:
Data looks something like this:
Application_Name Business_score Technical_Score
AAAAAA 1.2375 4.3289
BBBBBBBB 3.75297 2.43849
CCCCCCCCCC 4.53289 4.98367
I have the following crosstab query:
TRANSFORM First(TmpTable.Technical_Score) AS FirstOfTechnical_Score
GROUP BY TmpTable.Application_Name
This will be used as the row source for a Microsoft Graph DataSheet for an XY Scatter Chart.
It produces a result like this:
Application_Name 1_2375 3_75297 4_53289
The Problem is I need the numeric column headers to have decimal points instead of underscores, like this:
Application_Name 1.2375 3.75297 4.53289
I have tried setting the rowsource of the Chart to the query and then looping through the resulting Datasheet and changing the underscores to decimal points, but have not been able to get that to work. In any case, even if I could get it to work, the process of interacting with a Datasheet is too slow (many seconds, sometimes minutes, depending on how many points there are in the chart).
What I want to do instead is to alter the query above to produce the column headers with decimal points, or create another query that will take the results of the first one and produce the desired result. If I can do that, then I can set the rowsource of the Chart to that query and the chart will update "instantly" (or close to it).
How can I do this?
IMPORTANT NOTE: Someone has already suggested I move to a Pivot Chart, but re-orienting my app to that concept will require significant redesign and I need a more immediate solution for now.