Link to home
Start Free TrialLog in
Avatar of rprineppi
rprineppi

asked on

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)

Rob
Avatar of flavo
flavo
Flag of Australia image

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
UNION ALL
SELECT "Feb" AS Month, Sum(Feb) AS Total FROM tblYourTableName
UNION ALL
SELECT "Mar" AS Month, Sum(Mar) AS Total FROM tblYourTableName
UNION ALL
etc......

Save it and base you chart off this.

More help??
Dave
Avatar of rprineppi
rprineppi

ASKER

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:
fileds
      CustNum
      StartMonth ( the month the person joined the program)
      Category
      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.
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.

Regards,
Rob
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?
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.

Rob
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.

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

SUBFORM
 
 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
ASKER CERTIFIED SOLUTION
Avatar of flavo
flavo
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start 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.

Cheers!

Dave
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.
Rob
Ok.
Hasn't come yet..

It is hotmail co uk

Don't forget to zip it up too

Dave
Sent at 6:42pm Perth time to underway...@hotmail.co.au - zipped up to 539kb - hasn't bounced back yet so hopefully still on its way.
Rob
...@hotmail.co.au

it is

...@hotmail.co.uk
                    ^^^^

I'm in the London at the moment. Used to live in Brisbane though :-)
Sorry - mis type was above not in the e-mail I sent. The original went to ...@hotmail.co.uk. Will send again anyway.
Dave, just seeking confirmation - have you received my e-mail yet?
not yet :(
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.

Regards,
Rob