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
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
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
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
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.
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.
ASKER
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
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?
What happens next year? You going to have another 48 fields?
ASKER
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
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
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
It is hotmail co uk
Don't forget to zip it up too
Dave
ASKER
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
Rob
...@hotmail.co.au
it is
...@hotmail.co.uk
^^^^
I'm in the London at the moment. Used to live in Brisbane though :-)
it is
...@hotmail.co.uk
^^^^
I'm in the London at the moment. Used to live in Brisbane though :-)
ASKER
Sorry - mis type was above not in the e-mail I sent. The original went to ...@hotmail.co.uk. Will send again anyway.
ASKER
Dave, just seeking confirmation - have you received my e-mail yet?
not yet :(
ASKER
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
Regards,
Rob
I assume this could be done with a simple select query.
Can you post your table structure of this data.
Dave :-)