Variable Number of Plots in an MS Access Chart

Question

I have the following problem with MS Access:

Suppose I have a list of companies with monthly performance values. I can view the performance of a single company in a chart by hooking the chart into a query with a Month column and a Performance column.

Now suppose I want to display a chart for N companies. I could theoretically do this if I were to generate a query with a Month column and N Performance columns (one for each company). Is there any way to create a query with a variable column count like this? I have a SQL backend that I can use if necessary, and I'm fine with putting together any VBA code necessary to support it. The only impediment I'm seeing is that I'm stuck using MS Access, which I am not very familiar with.

So here are my main questions:

1. Is this even possible?
2. How would I go about tackling this issue? I'm trying to minimize research time, so it would be great if I could just get pointed in the right direction.

Thanks!

Answer 1

With this table:

company pmonth performance
      1      1          10
      2      1           8
      3      1          15
      1      2          15
      2      2           5
      3      2          25
      1      3           5
      2      3           4
      3      3          20

I create this query:

SELECT p.company, p.pmonth, p.performance
FROM MonthlyPerformance AS p;

Then change the query to PivotChart View and drag company field to "Drop Series Fields Here", drag pmonth to "Drop Category Fields Here", and drag performance field to "Drop Data Fields Here".

If you prefer, you can create a form using the same query SQL as its data source, then set the form's Default View to PivotChart, and set up the chart the same way as I did for PivotChart view on the query.

If that's not what you want, give us some more information about the type of chart you want and the context in which you will display it.