Thread: Dynamic Graphs using a table?
06-09-10, 10:14 #1Registered User
- Join Date
- Jun 2010
Unanswered: Dynamic Graphs using a table?
I am stuck in both Excel and Access trying to achieve the following but I'm thinking I'm over thinking it and getting confused in the process. Any help would be much appreciated.
Every month, I generate a report from an external source and create pivot tables/charts manually to allow the team to get a better visual on the data trends. However, given that the monthly report has too many rows per month, it wasn't ideal to keep using Excel because an entire year's data will not fit.
As an example, the table/data source has the following:
- Primary key (e.g. Family ID)
- Family Name
- Family Member Name
- Car (e.g. each member can have multiple cars)
- Fuel Type (e.g. each car can be petrol, diesel, hybrid or LPG)
- Kilometres Travelled
- Number of Re-fuels
- Refuel Rate (e.g. refuel / kms travelled * 100)
I figured I could store the data in Access and possibly have a form where it will have the following:
- the form will simply have a drop down box where user can select a primary key.
- based on the selected primary key, a pivot graph and/or table will be dynamically produced.
- To make it slightly complex, this might seem ambitious but there will be 3pivot graphs and/or tables that will be created. A pivot table/graph using the following columns:
For each pivot table/graph, it will show the KMs travelled, Number of refuels, and refuel rate.
I hope this was clear, happy to provide some more info and thank you for your help.
06-11-10, 10:50 #2Registered User
- Join Date
- Oct 2009
well I'm not sure I see a specific question - - but the issue you have is one I see all the time. In 'excel logic' users acquire a horizontal slide/scroll of infinite width. And then have no way to print that....
In databases records are always stored vertically.
So the pivot table/graph is often relied upon to bridge the two - - i.e. database storage but excel style display. Like most kludges it often isn't satisfactory....because again you end up with more width than can be printed. Plus it is hard to manipulate data in real time.
I wean users off excel logic - - once they get accustomed to grouped data, or indentation or whatever method helps - even colorizing font -- eventually they get use to it but can be very resistant to change - - if printing is a key issue then getting them within a page width landscape mode really dictates the layout ....
06-11-10, 12:04 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if you can think of a way of asking the question "give me the the data that matches the following criteria" then its relatively trivial to create these graphs in Access. the charting wizards in Access and Excel are very similar.
if you can express those criteria in English, then its not that big a step to translate that into SQL and use that SQL as the feedstock for the chart(s)
you can have mulitiple charts per report page (I've seen some with 10 or 12 charts per side of A4)I'd rather be riding on the Tiger 800 or the Norton