My client wants a report with a chart that requires a secondary axis. I can produce such a chart but, quite frankly, it looks awful. Most values on the primary axis are positive but there is one that is a rather large negative. The primary axis is plotted as a column chart. The main problem with this part of the chart is that the axis label falls inside the chart itself. Playing with the offset I am not able to get it to move more than half way to the bottom of the plot area.

The secondary plot is also terrible. I have played with colour and weight for the line but cannot get a line joining all the dots. Are there any charting alternatives that will give a more pleasing result?

Glenn

I just tried a basic bar chart with similar data to your's and it's not that bad. It puts the axis labels on the bottom on the x-axis, so they don't interfere with the chart. Have you tried that? The other one is the Custom Type Column - Area chart.

Thanks for the suggestion.

However, quite frankly, the results are no better. When you say a 'basic bar chart', do you mean one without a secondary axis? The two data ranges are of quite different magnitudes so the secondary axis is needed to scale the markers reasonably.

Glenn

Let's see if I understand.

You have a NetInvestment and then a MarketValue. I'm left with the impression that the NetInvestment is basically the difference in the total value of the investment.

If so, wouldn't it then work to add NetInvestment + MarketValue, chart those values, then label the NetInvestment's axis to scale with the change. Your market axis would be then more sensible?

Market Value and Net Investment are completely independant of each other. Net investment is a stream of deposits and withdrawals that I put into the pot. Market value is what the market says the pot is worth on any given day. In the chart the net investment figure is how much I put in each year. The pot continually grows but the net investment number for any year is just what I put in that year. The market value, on the other hand is the value of the cumulative amount of all net investments.

Another way of looking at it is that net investment is the amount that goes through my bank to acquire units. Simplistically, the market value and net investment are identical at the moment I buy but after that the market value varies. So if I buy 10 units at a market value of $1 per unit, my net investment for that period will be $10. Now the market value drops to 0.50 but I invest another $10 and I get 20 units. The market value of the thirty units I now hold is $15.00.

Over the long term, my net investment annually will be in a relatively low order of magnitude. If all is well, the market value will grow over time. If there were no change in market value, in just two years of depositing $10 per year, the market value will be double my annual net investment.

In short, you can't just add them together.

Glenn

Glenn,
Not sure if you are still looking for an Access created chart but you might want to look at the attached.

Hi Duane,

Ideally I would like an Access created chart. Your attachment takes me about 99% of the way. I assume that I had overlooked adjusting the scale of the net investment access. I am on a client site today so can't test it but I will asap. There is still a problem with the line not rendering correctly but I will try playing with that.

Thanks

Glenn