Graphing calculated fields from another sub-form

I have created a form that allows the user to select a client then displays related information via several tagged sheets each of which has subforms. One of the subforms looks something like the following:

Category     Jan  Feb   Mar   Apr .......Dec  TOTAL
A                100 150   120   100        130   2700
B                  50   45    70     90          70   1200
CUM-SUB     150  345  535   725       3900  

The CUM-SUB row as well as the TOTAL column are calculated fields
I would now like to create a graph in another subform that displays the CUM-SUB data by month - in other words the X-Axis needs to be the months. I have tried to create the graph but have come up against a couple of problems that I don't know how to tackle and seek your help (I am an Access novice).
1. How do I refer to calculated fields from one sub-form in another sub-form?
2. How do I get the graph to display all 12 months (when I used the wizard it said I could only use 6 variables)

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi rprineppi,

I assume this could be done with a simple select query.  

Can you post your table structure of this data.

Dave :-)
I had a look at your last Q and found you structure.

Create a new query like this

SELECT "Jan" AS Month, Sum(Jan) AS Total FROM tblYourTableName
SELECT "Feb" AS Month, Sum(Feb) AS Total FROM tblYourTableName
SELECT "Mar" AS Month, Sum(Mar) AS Total FROM tblYourTableName

Save it and base you chart off this.

More help??
rprineppiAuthor Commented:
Hi Dave,
Thanks for your help. I am afraid I will need more help. I don't quite understand where I need to put your code to create the result, as I said I am a novice at Access, although I am fairly proficient at Excel.

Just to clarify, the structure I am working with is actually far more complicated than explained above but I think if I can figure out how to make it work in the simplified example above I hope to be able to apply it to my real structure. The real structure now is:
      StartMonth ( the month the person joined the program)
      P1M01  to   P1M12   (plan1 for months 1 - 12)
      P2M01  to   P2M12   (plan2 for months 1 - 12)
      A1M01  to  AP1M12   (Actual1 for months 1 - 12)
      A2M01  to   A2M12   (Actual2 for months 1 - 12)

What I have done in one of the subforms for the selected customer is to calculate the running cumulative subtotols of P1Mxx + P2Mxx (I called them PSM01 to PSM12 on the form) by category, bymonth and then calculated the running cumulative subtotals of A1Mxx + A@Mxx (called ASM01 to ASM12), then I calculated the variances and display them for comparison.

Now I need to graph the cumulative subtotals by category by month so we can easily see the trend.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

rprineppiAuthor Commented:
I assume the absence of a reply or further comment means my structure and expectation is too high for the available points. If I change the structure to that reflected by earlier Dave's comments above, how do I get it to work? I don't quite understand where the Select statements go. If I can figure that out I might be able to simplify my structure to better achieve at least part of what I am trying to achieve. Any comments and help would be greatly appreciated.

I was out all weekend... I'll have a think now
So your table has 50 fields right?

What happens next year?  You going to have another 48 fields?
rprineppiAuthor Commented:
Thanks Dave.

My assumption was wrong - I wasn't trying to put pressure on you. I am calling it quits for tonight but I'll look forward to any comments you may have for me tomorrow. I appreciate your ongoing support.

rprineppiAuthor Commented:
Hi Dave,

Hope you had a good weekend away.

Actually the table has a horrifying 53 fields - I didn't mention it also has a sub-cat field and a Notes field too. And so next year it will have another 53 fields. It would be neat to be able to see last year's actual against this year's actual and plan on the same sub-form but I thought that would be pushing it so I have created another sub-form on a separate tab that shows last year. I would have preferred to set it up with a more straightforward structure but an earlier question convinced me this structure would be easier.

Essentially the form & one sub-form looks like the following. It displays al the data I need to analyse the member's progress. Now I need a graph (on a separately tabbed sub-form) to show the trend of Cumulative plan against cumulative actual. It would be perfect to have both the monthly actuals and the cumulative totals on the same graph (different scales as is possible in Excel graphs) but I suspect this may be asking too much. I will eventually need to draw the graphs on a printed report too.

That gives you an idea of what I am trying to achieve as the end product, not that I'm trying to get it all accomplished in this question - I know I will have many more questions to post before I finish. Any help you can give me to get this stage working will be appreciated.

      CustNum  1223      (Name & other details retrieved from linked Cust table display in header)

 Category                                   StartMonth     StartMonth+1   StartMonth+2  ......  StartMonth+12
 Categoryvalue  Plan1Label          P1M01value    P1M02value      P1M03value    .....   P1M12value
 subcatvalue     Plan2Label           P2M01value   P2M02value       2M03Value     .....   P2M12value
 Notes              SubTot label        PM01subTot   PM02SubTot     PM03SubTot.  ....    PM12SubTot
                       CumSubLab         PM01Cum      PM02Cum         PM03Cum       ......  PM12Cum
                       Act1Label            A1M01value   A1M02Value      A1M03Value   ....    A1M12Value
                       Act2Label            A1M02value   A2M02Value      A2M03Value   ....    A2M12Value
                       SubTotlabel         AM01subTot  AM02SubTot      AM03SubTot  .....   AM12SubTot
                       SubVar%Label     Var%1          Var%2               Var%3          ......  Var%12
                       CumSubLab         AM01Cum      AM02Cum         AM03Cum      ......  AM12Cum
                       CumVar%Label    CumVar%1    CumVar%2        CumVar%3    ......  CumVar%12

......Next category on continuous form
>> I would have preferred to set it up with a more straightforward structure but
>>an earlier question convinced me this structure would be easier.

Before I read any further, I tend to disagree.  No offence (To you or the other "expert"), but this is a terrible design.  

I'll have a closer look at the rest of your question and see what I can do...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Can you post your db up to a website?  I'd like to have a look at it and see what i can do.

Just keep a few categories’ worth of data (nothing top secret of course) or change the data if you need to.  Do a compact and zip it up.


rprineppiAuthor Commented:
Hi Dave,
I don't have a web site I can post it to so I have e-mailed it to the e-mail address in your profile. Hope you don't mind and I hope you can figure out what I am trying to do.
Hasn't come yet..

It is hotmail co uk

Don't forget to zip it up too

rprineppiAuthor Commented:
Sent at 6:42pm Perth time to - zipped up to 539kb - hasn't bounced back yet so hopefully still on its way.

it is

I'm in the London at the moment. Used to live in Brisbane though :-)
rprineppiAuthor Commented:
Sorry - mis type was above not in the e-mail I sent. The original went to Will send again anyway.
rprineppiAuthor Commented:
Dave, just seeking confirmation - have you received my e-mail yet?
not yet :(
rprineppiAuthor Commented:
I am assuming that this is too difficult for the points so I will close it out. Thanks for offering to help. I will try to simplify my needs.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.