[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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!
0
billg7
Asked:
billg7
  • 5
  • 4
1 Solution
 
billg7Author Commented:
oh and by the last line "I tried putting it in the filter..." I meant tried putting it in the FOOTER :)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access 2010 allows you to create "dashboard-type" forms, where you can intermingle reports with standard Form constructs. Is that what you're referring to?

Can you upload your database (with the sensitive data removed, of course) so we can see what you're doing?
0
 
billg7Author Commented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
billg7Author Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.Report
  .Filter = Me.Filter
  .FilterOn = True
End With
0
 
billg7Author Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
billg7Author Commented:
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now