Link to home
Start Free TrialLog in
Avatar of wsturdev
wsturdevFlag for United States of America

asked on

Crosstab Query with Decimal column Headers for MS Graph Datasheet

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.
Avatar of HartCraft
HartCraft

I don't know why that would happen, but maybe doing a simple replace of the underscore with a decimal would work:

TRANSFORM First(TmpTable.Technical_Score) AS FirstOfTechnical_Score
SELECT TmpTable.Application_Name
FROM TmpTable
GROUP BY TmpTable.Application_Name
PIVOT Replace(TmpTable.Business_Score,"_",".")
Avatar of wsturdev

ASKER

It still produces the underscores.  Apparently Access internal logic assumes the values for Business_Score will be used as field names, and a period cannot be included in a field name.
Avatar of Kevin Cross
Correct.  That is what a PIVOT or CROSSTAB is.  The values of a specific column is turned into columns horizontally for each different value.  You can try that approach, replacing the "." with another character although I would suspect you don't actually want to use PIVOT / TRANSFORM here.
Okay, I re-read and see you really do want the decimal values as the column headings... Might be very tricky.
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked!  I figured I would go for the easy one first - the XY Scatter.  Now I need to extend the concept to accommodate data for a Bubble chart.
Add 1 more field for every record:  Bubble_Size.  So the table I start with looks like this:
Application_Name     Business_score      Technical_Score         Bubble Size
AAAAAA                                 1.2375                    4.3289                                2
BBBBBBBB                          3.75297                 2.43849                              5
CCCCCCCCCC                  4.53289                4.98367                              4
The query needs to produce this:
Application_Name             1.2375        3.75297          4.53289    
AAAAAA                                 4.3289
Size 1                                          2
BBBBBBBB                                              2.43849
Size 2                                                               5
CCCCCCCCCC                                                              4.98367
Size 3                                                                                        4
 
Let me guess. Each company occurs only once, so you didn't really need the pivot table in the first place. However, not only do you need each bubble in a different colour, you also need the legend or data label for the company. Is that correct?

If it is, I'm afraid you will have to study pivot tables (pivot charts?). I can't help you much with them, but the data you need cannot be created in any simple way: you need two cross-tabs (one for the scores, one for the bubble size), which then need to be assembled in a third query. Since you have dynamic columns, this third query probably needs to be created through code.


For the exact layout you want (scores as column titles), you can also multiply your scores by 1e6, and then use a special format to divide the scale unit by 1e6 (format: "0,,", using two thousand separators to divide by thousand twice). This would be another answer to your original question.

But then comes the problem of assembling rows with different source, perhaps a UNION query?


In any case, this has turned into quite a different question, even if it looks similar. Bubble charts are not just a special kind of XY charts.

(°v°)
<Each company occurs only once, so you didn't really need the pivot table in the first place.>  Each Application Name ("company") does occur only once, but I am not sure I follow the remainder of that comment.
The only reason I even got involved with a Pivot Table was that I needed a specific array of data in the Datasheet for the XY Scatter Chart, and another slightly different array for the Bubble Chart.  Originally, I was looping through code and filling in the appropriate cells in the Datasheet, but it was way too slow.  I could not find any way to generate an array and then "drop" it onto the Datasheet in a single command (e.g. Datasheet = Array).
The only apparent way to generate a really fast array of the type I needed and apply it to the Datasheet is to use a Crosstab query and set the Chart row source to it.  And the SQL for such a query apparently automatically generates a Pivot Table.
Any way, the solution for the query to fill the datasheet for the XY Scatter as identified above works very nicely.
Before I read your comment posted immediately above, I had thought of trying to use some sort of Union.  I worked the following out, and came here to post it when I saw your latest comment:
TRANSFORM First(Combined_Records.Point_Or_Bubble_Size) AS FirstOfPoint_Or_Bubble_Size
SELECT Combined_Records.Business_Score
FROM (SELECT TmpTable.[Application_Name], Application_Name AS App_ID,
TmpTable.[Business_Score],  
TmpTable.[Technical_Score] AS Point_Or_Bubble_Size
From TmpTable
Union
SELECT TmpTable.[Application_Name], Application_Name&1 AS App_ID, TmpTable.[Business_Score],
TmpTable.[Bubble_Size] AS Point_Or_Bubble_Size
From TmpTable
ORDER BY App_ID)  AS Combined_Records
WHERE (((Combined_Records.App_ID) <> ''))
GROUP BY Combined_Records.Business_Score
PIVOT Combined_Records.App_ID;
It produces a result like the following (and I have set the chart to "Data in columns"):
Business_Score         AAAA           AAAA1          BBBB        BBBB1         CCCC          CCCC1
1.2375                         4.3289              2
3.75297                                                                2.43849            5
4.53289                                                                                                           4.98637            4
It does not matter whether the Column Header for the bubble sizes is "AAAAAA1" or "Size 1".  It only matters that the bubble sizes appear in the column immediately to the right of the column with the value of the point to be plotted.
Just FYI, as a point of comparison, building the datasheet for the XY Scatter the old way (looping through records and putting values into datasheet cells) for 240 points used to take 1.4 minutes.  Now it takes about 2 seconds.
You are also correct in that the number of points in the XY Scatter will have the same limits as the number of columns in a Crosstab query (255?).  I will test later, but I assume that means I will be limited to half that for the Bubble chart because it takes 2 columns for each Application Name.
I will put some logic in to check the number of points to be plotted and advise the user they must either filter the records to a more manageble set to get a fast chart, or if they choose to go for the total number of points, I will do it the old way and use my popup dialog form to show them how long it will take.
That is... unless you know a way to create an array and "drop" it onto the datasheet...
Good job! you are more experienced that I suspected (I wasn't going to suggest that type of query yet)...

You can make it slightly faster by using UNION ALL and removing the unnecessary ordering in the inner query. Given table Data(L,X,Y,S) -- label, X, Y, Size -- the query would be:

  TRANSFORM First(T.YS)
  SELECT T.X
  FROM (
    SELECT L As Col, X, Y As YS FROM Data
    UNION ALL
    SELECT L&1, X, S FROM Data
    ) AS T
  GROUP BY T.X
  PIVOT T.Col;

Note: if you need filtering (your AppID<>''), perform it in the innermost queries, not in the cross-tab query.

Something similar can be done for data in row (and multiplying the X field to change it to a long integer): a resorted UNION of two cross-tabs.

You didn't answer the question about the legend. If you don't need the legend, you can use this as source:

  SELECT X,Y,S FROM Data;

(Use the option "vary colours by point" if you like this look.) The difficulties all arise from the fact that you are trying to create one series for each data point, and this has meaning only if you want a legend. Since you worry about the 255 column limit, you might not need a legend at all. At least not one embedded in the chart.


Notes:

* As far as I know, you cannot assign an array to a Graph.Range. The very similar Excel.Range offers these implicit conversions (range to array and array to range), but not the simplified Graph implementation.

* The only way to "dump" data into the DataSheet is through RowSource (there is no exposed RecordSet object): a query or a temporary table.

* A Graph.DataSheet has more that 255 columns (is it 4000?); the limiting factor for Access is the number of columns returned by a query.


(°v°)
I will look into this some more later today...  the "boss" is demanding I do some other things for a while...
Regarding the legend, the user has an option to show it or not.  The issue is the space in which to show the graph will not allow very many entries in the legend.  I give the user an option to show a modal dialog with a color coded list of application names.  Once the graph is prepared, I give the user an option to transport it to Word or PowerPoint.  At that time I can also transport a color coded list of the application names along with the graph.
Generally, though, when a large set of records is shown in the graph, the point is to see the general clusters of where the entries plotted out, not to be eble to discern a single point.
This gives you the following solution to avoid having to build the data through code.

Up to, say, a dozen applications selected, use your cross-tab of a UNION query so that each bubble is in its own series, thus allowing a legend. If more applications than your limit are selected, create a single series (three columns: X, Y, Size). A colour coded legend has not practical use above one or two dozen applications, and certainly not when you reach the limit of allowed columns in a query!

A much better use of colours in a bubble chart with dozens or hundreds of applications would be to show one additional information, e.g. a category. You could also "tag" certain applications based on a criteria: blue bubbles background, red bubbles corresponding to the user's current criteria or selection...

(°v°)
I have tested some of this and have further questions...
1. When I select X,Y,S, the chart shows all points as a single color and type of marker, with the exception of 3 points, which appear as some other marker with some other size and color.
2. How would I incorporate a "category" or "tag" into my query?
Forgot to finish the first question...
1. When I select X,Y,S, the chart shows all points as a single color and type of marker, with the exception of 3 points, which appear as some other marker with some other size and color.  Why is that?
Guess I am tired this morning...
With respect to bubbles, I have been requested by the user to always show the bubbles with the largest at the back.  How do I change my crosstab query to accommodate a descending order by size?
1. Probably because these bubbles have been changed individually. The series will remember that, say, points 11, 13, and 18 have a non-standard colour.

2. You can use a union query to show "red" bubbles as X, Y, S, Null, Null, Null, Null, "blue" bubbles as X, Null, Null, Y, X, Null, Null, and "green" bubbles as X, Null, Null, Null, Null, Y, S

3. You need to sort by descending size.

You realise, I hope, that your additional questions no longer have anything to do with your original question. Please close this question, and use "ask a related" question if you think there is still some vague connection to this one.

Cheers!
(°v°)
Well, I thought all this discussion was a continuation of the original question, but if need be, I will ask a related question.  How can I be sure you will pick it up -- you have been giving me excellent help and I would hate to lose you!
In the meantime, to close this out...
1. I fixed this manually, but would rather have known how to do it programmatically.
2. I will check this out
3. I had tried this but failed, which is why I asked the question.  But now I have played with it further and succeeded.
And finally...  I have posted a separate question.  The subject is "General MS Graph problems in Access 2007".  I hope you will jump into it.
Sorry - one last thing -- where can I find the most detailed discussion of programming MS Graph within Access?
@wsturdev:

Now that you have closed out the question appropriately, you should see a link above the box where you would post a new comment that says "You can also ask a related question."

That will send an e-mail to the participating Experts such as harfang.  

Good luck with the follow-up questions.

Best regards,

mwvisa1
wsturdev,

> you have been giving me excellent help and I would hate to lose you!

Thank you for that, and I will look up your next question (as mwvisa1 explained, it is easier for me to track it if you use "ask a related question").

I would however like to explain something about Experts. We each choose to give a certain amount of time to this site, but not necessarily on a regular basis. In that time, the feeling to "get something done" is important, and this is by seeing questions being closed (and getting a thank-you and some points).

I might be interested in your next question and I might have time for it, or not. In any case, a new question will attract other experts, some perhaps more qualified for a given direction your questions are taking. I will be free to follow or not, knowing you will get help anyway, and we might meet again in some other question. I don't mind exploring a little around an initial question, but the system works best when asking new related questions -- for Experts and Askers alike.

Cheers!
(°v°)
mwvisa1 -- unfortunately, I had never "asked a related question" before, and posted a separate one too quickly.  I will keep that in mind in the future.  Thank you.
harfang --  I do really understand your situation, and do really appreciate all the help I have gotten from the experts since I have been using the service.  I am amazed at the amount of time you folks give to helping others, and a bit envious.  I sometimes wonder if you people have "real jobs"... how can you afford the apparent luxury of just spending your time answering questions!!
I wonder that myself, for some of the Experts. The only time I was "expert of the month", I was indeed unemployed, and did nothing else but answering questions. This helped me a lot in maintaining my self-image, professionally and emotionally, by the way.

Normally, answering questions is more like training. In IT, you need to spend about one quarter of your working hours learning. If you stop for a couple of years, it's hard to climb back on top. I learned a lot by searching for solutions on EE, and I find it more pleasant (and more rewarding) to tackle real-life problems than to invent tasks for the sake of learning. Incidentally, I'm also exercising analytical skills, communication, and English.

To me it's not so much a luxury. It's really more a choice about how to exercise, which is a necessity.

(°v°)