Access Form Dynamic Summary Chart and Sums
Hi Experts,
I have a form that has a filterable dataset on it with the following fields:
Employee Name
Work Week
Work Code
Hours
The data can contain several of the same employee name, work week, work code and hours. In other words, you could have 10 lines that look all the same. The data is then filterable, of course.
I'd like to create sort of a dynamic dashboard on the form that shows a pie graph that sums up hours spent by work code, and then by employees. I'd like the graph to change when the filters are applied as well.
I can't even get the graph to show up on the page. I tried putting it in the filter with no luck. I'd love some feedback on how to do this, if even possible. Thanks in advance!
I have a form that has a filterable dataset on it with the following fields:
Employee Name
Work Week
Work Code
Hours
The data can contain several of the same employee name, work week, work code and hours. In other words, you could have 10 lines that look all the same. The data is then filterable, of course.
I'd like to create sort of a dynamic dashboard on the form that shows a pie graph that sums up hours spent by work code, and then by employees. I'd like the graph to change when the filters are applied as well.
I can't even get the graph to show up on the page. I tried putting it in the filter with no luck. I'd love some feedback on how to do this, if even possible. Thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, here's the DB. I'm running Office 2010 but I believe the DB was created in 2007... Thank you!
Desktop-Time-BAD-Projects-v0-5.accdb
Desktop-Time-BAD-Projects-v0-5.accdb
I don't see where you've started this sort of layout - which form were you using to try and build this?
As I said, with 2010 you can build a report that would show what you want (the pie chart, for example) and then embed that report on your form. You could then filter the report with the same filters used in the Form.
However, I see that you're using embedded macros. This sort of advanced functionality almost always requires the use of VBA code. Are you comfortable with VBA?
As I said, with 2010 you can build a report that would show what you want (the pie chart, for example) and then embed that report on your form. You could then filter the report with the same filters used in the Form.
However, I see that you're using embedded macros. This sort of advanced functionality almost always requires the use of VBA code. Are you comfortable with VBA?
ASKER
I had been trying to put it in the 'Work Hours List' form. But that wasn't working for me. The version i uploaded doesn't have those changes. I will try building a report based on that (although I'm none too good with the report builder either and will have to figure that one out today)...
I assume I would need to use the datasource (table or query) that the form uses to produce the report as what will be used on the form.
I am not at all very comfortable with VBA, although I was able to add that VBA code with some assistance. I'm a real n00b with this stuff, but I have done coding before and am quickly picking it up.
I'll try getting the report nailed down first today.
I assume I would need to use the datasource (table or query) that the form uses to produce the report as what will be used on the form.
I am not at all very comfortable with VBA, although I was able to add that VBA code with some assistance. I'm a real n00b with this stuff, but I have done coding before and am quickly picking it up.
I'll try getting the report nailed down first today.
Yes, I would assume you'd want to base that report on the same recordsource. If you're just filtering, you can most likely do something like this in the Filter event of your form:
With Me.YourSubformControlName.
.Filter = Me.Filter
.FilterOn = True
End With
With Me.YourSubformControlName.
.Filter = Me.Filter
.FilterOn = True
End With
ASKER
Ok, I think that makes sense... I did create a report to use as an example. The updated DB is attached. I wanted to place the report, called 'Work Hours by Employee' into the 'Work Hours List' form. I can't get that to work...
Desktop-Time-BAD-Projects-v0-6.accdb
Desktop-Time-BAD-Projects-v0-6.accdb
Change the main form's Default View to "Single Form" and see if that makes more sense. You can always lay out the Textboxes in the Detail section to look like a Datasheet view.
Your report's chart is in the Page Header. I don't think you can do that with embedded reports - it would need to be in the details section - and if this is a "dashboard" type of setup, then that would be the preferred view, I would think.
Your report's chart is in the Page Header. I don't think you can do that with embedded reports - it would need to be in the details section - and if this is a "dashboard" type of setup, then that would be the preferred view, I would think.
ASKER
Hmm, well, I was unable to accomplish this, but I think I got the reports that I needed. Thank you for your help! I will go ahead and close this item.
ASKER