Graphing a value between two dates

[amcalabrese]

Reference the attached picture, how do I set up a graph to plot the "spread" values between and including the two dates?



I'm trying to draw a stacked area chart for the "spread" values between EAD and LAD. It seems easy enough in theory, but I'm having a hell of a time trying to do this.

Also, I'd like to keep it in a query, because while the example shows only 6 records, my database at work has over 7,000 records.

Any help would be appreciated.

[kennejd]

I'm having a hard time visualizing your requirement. Excel is an easier method to get started on a graph. Can you create a graph in Excel that looks the way you want? Then we can try to duplicate it in Access.

[amcalabrese]

Ok, so I used Excel to graph out what I am trying to accomplish with Access. It's called a Sand Chart. It's pretty basic in Excel. But the format is much different in how the data is stored in access. I think the way ahead is to have a query calculate an array, and then graph the results. Two problems with this... 1) I don't know how to get access to create an array as the result of a query, and 2) I have not idea how to graph an array. What are your thoughts?

[kennejd]

That looks like a basic crosstab. If you create a form and add a chart control, you can follow the wizard to create a chart. Access will build the crosstab query for you based on your inputs....might take a few tries to get it to look ok.

[amcalabrese]

I've been thinking about it for some time now, and I think I have a way ahead, though input is always welcome.

I think if I write some VBA code on the form load event, I might be able to get it to do what I want. Esentially, I know my starting place will be [EAD], with a value of [STON Spread]. I must then create a do-loop to create multiple columns with the same value of [STON Spread] until I reach [LAD], or my end date. I think this would be a dynamic query.

Thoughts?

[Poolio]

Hi there...

Re:

I think I have a way ahead...

What is this way ahead of which yuo speak??!

I am trying to compile a similar thing (I think)...

I have projects which run from StaDate to EndDate, which have an equal spread of values across those dates, which I want to present in a Stacked / Sand(?) chart.

Any chance you can embelish on what you did to solve your problem

...write some VBA code on the form load event.... create a do-loop to create multiple columns with the same value ... until I reach my end date. I think this would be a dynamic query....

This sounds well beyond my Newbie knowledge... I can make it all work in Excel!

Cheers
Poo