troubleshooting Question

Crosstab Query with Decimal column Headers for MS Graph Datasheet

Avatar of wsturdev
wsturdevFlag for United States of America asked on
Microsoft AccessSQL
21 Comments1 Solution1219 ViewsLast Modified:
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:
Application_Name (Text)
Business_Score (Number)
Technical_score (Number)

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
SELECT TmpTable.Application_Name
FROM TmpTable
GROUP BY TmpTable.Application_Name
PIVOT TmpTable.Business_Score;

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    
AAAAAA                             4.3289
BBBBBBBB                                             2.43849
CCCCCCCCCC                                                               4.98367

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    
AAAAAA                             4.3289
BBBBBBBB                                             2.43849
CCCCCCCCCC                                                               4.98367

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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 21 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 21 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros