Connecting a Risk Cube or Graph from Excel into Access and use a query to populate the graph

[kingofthekassel]

Hi,

So I recently exported a bunch of data from an old online database


into Excel. Using this data I used a couple VLookups, Drop down menus and most
importantly some simple VBA code to create a Risk cube. My problem now is that I
need to transfer all of this into Access in order to make reports. So far I
successfully imported all of the data into Access but I have hit a snag with the
Risk cube. I created a form with the necessary fields but it is lacking the
cube. Is there any way to transfer the Risk cube (in it's working status) from
Excel to Access? Here are the criteria I need the cube to follow:
1) The Risk Cube must contain three series (Initial, Current, and Target)
2) Each Series has the coordinates based off of their respective Consequence and Probability
3) I want to include this in a report so I need it to work for every individual risk.
4) I need this to be tied to my existing Query so that it will update with any changes.

I have had a couple ideas of how to approach this but I don't know how to go about any of them.
1) Figure out a way to export the working Risk Cube with all parts working (doubtful)
2) Create the graph in Excel, without any data. Export this graph to Access and attempt to populate the graph with the data from the Query I made. (Most likely)
3) Or Figure out a way to do everything in Access.

These seem to be my options as of right now. I welcome openly any other suggestions. Please help, I am so stuck here.

[June7]

This is just a graph? Can you attach an image? And provide sample data. If you want to attach db for analysis, follow instructions at bottom of my post.

I suspect the graph will have a CROSSTAB query as its RowSource.

[kingofthekassel]

I appreciate the fast response. Here is a small piece of my query. The "Probability" and "Consequence" Fields represent the Target Probability and Target Consequence. Additionally, In the image I attached, the symbols on the graph are the three different representations of each series (Initial, Current, Target).

RiskNo	First Probability	FirstConsequence	Current Probability	CurrentConsequence	t1.Planned Complete	Probability	Consequence
AW2	4	4	2	3	11/4/2015	1	3
BU13	3	3	4	3	5/29/2015	4	3
FI9	4	5	2	3	11/28/2016	1	1
FI10	4	4	1	2	2/13/2014	2	2
KQ2	2	3	1	1	8/14/2015	1	1
CU9	3	4	2	4	2/27/2015	1	1
RT1	1	2	1	2	11/28/2016	1	2
RT2	2	2	2	2	8/9/2014	2	2
LK4	1	2	1	2	8/9/2014	1	2

[kingofthekassel]

I think perhaps the easiest route may be to simply import this graph (shown in the attached image) to access and then populate it with the data from the query. If we could do this, is there any way to make each graph unique to each RiskNo?

[June7]

Cannot import graph. Must build from scratch in Access.

I can't correlate the data with what is presented on the graph.

What purpose do the symbols serve - why are they all in the same location on graph? What is the data that places those symbols? What do the colors represent - risk level? Which field provides this value?

Never seen a graph like this and really don't know if can be reproduced in Access.

[kingofthekassel]

So the x-coordinates are the fields that contain "consequence" and the y-coordinates are the fields that contain "probability". all of those are in the same place because they represents a Risk like LK4 where all three series (First, Current, Target) have the same coordinates. The colors do represent risk level. I got this to work in excel, I just don't know how to do this in access.

[June7]

Okay, how to you graph the colors?

[kingofthekassel]

The colors are just part of the background on the graph. That part of the graph stays the same

[June7]

But how did you do it? What causes that 'background'?

[kingofthekassel]

Well, for my excel cube I just manually filled the cells with those colors and then took a screengrab of that 5x5 chart and used that as my fill for the background on the chart.

[kingofthekassel]

So in short, it's basically just a picture as my background.

[June7]

Okay, would have to do the same thing with Access. Use an Image control or OLEObject control to embed image and then have graph on top set as transparent.

However, no idea how to get graph to plot points with your special symbol icons.

[kingofthekassel]

Thank you for your help! Now I am not really well rehearsed in Access VBA, so would you mind helping me get an idea of how to do that?

[June7]

Your options 1 and 2 from original post are not possible.

No VBA is needed for basic graphing. I have never tried to use images as point symbols. Don't know if it is possible - I have doubts.

The graph would have to be a control on form or report.

[kingofthekassel]

Hey so I was reading some of your other posts on other forums and I maybe came up with a work-around.

Referring to this feed: https://www.accessforums.net/forms/n...ess-24894.html

How can I make it so when I cycle through each Form per RiskNo, I get its unique graph? I was thinking of doing three separate graphs for each "series" (Initial, Current, and Target) and setting them all to transparent background and overlapping them all. But To do this I need the graphs to show only one point at a time.

i.e. Referring to Previous Chart:
For RiskNo: FI9
Graph 1 (Initial): Point (5,4)
Graph 2 (Current): Point (3,2)
Graph 3 (Target): Point (1,1)