Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crosstab Query with Decimal column Headers for MS Graph Datasheet

Posted on 2009-12-24
21
Medium Priority
?
1,041 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:wsturdev
  • 10
  • 7
  • 3
  • +1
21 Comments
 
LVL 4

Expert Comment

by:HartCraft
ID: 26119816
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,"_",".")
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26119908
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.
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 26120181
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.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 61

Expert Comment

by:Kevin Cross
ID: 26120191
Okay, I re-read and see you really do want the decimal values as the column headings... Might be very tricky.
0
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 26121293
Hello.

I was called in for a second opinion. The short answer is: you can't.

A cross-tab is a special kind of query, but it's still a query. Column names follow the same rules as field names, and periods are not allowed. Periods are used as separator between the table name and the field name, allowing them in table or field names would break the SQL parser.

You can use periods in captions, but that will not solve your problem, the chart will not use the captions.

Given your data, you can however flip the cross-tab on its side.

TRANSFORM First(TmpTable.Technical_Score) AS FirstOfTechnical_Score
SELECT TmpTable.Business_Score
FROM TmpTable
GROUP BY TmpTable.Business_Score
PIVOT TmpTable.Application_Name;

If you have less than about 250 applications, this cross-tab will work, and can be used as source for a chart. Simply change the setting from "data in rows" to "data in columns".

Good luck!
(°v°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26121384
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
 
0
 
LVL 58

Expert Comment

by:harfang
ID: 26121583
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26122582
<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.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26122585
That is... unless you know a way to create an array and "drop" it onto the datasheet...
0
 
LVL 58

Expert Comment

by:harfang
ID: 26122759
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26122783
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.
0
 
LVL 58

Expert Comment

by:harfang
ID: 26122833
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26127340
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?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26127346
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?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26127372
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?
0
 
LVL 58

Expert Comment

by:harfang
ID: 26128011
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26133578
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?
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 26133615
@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
0
 
LVL 58

Expert Comment

by:harfang
ID: 26134508
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°)
0
 
LVL 1

Author Comment

by:wsturdev
ID: 26135487
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!!
0
 
LVL 58

Expert Comment

by:harfang
ID: 26135591
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°)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question