creating a graph from access "group by" data (1 Viewer)

vba_php

Forum Troll
Local time
Today, 10:34
Joined
Oct 6, 2019
Messages
2,884
hey you guys,

Does anyone here have knowledge of creating graphs in excel? I've always hated doing graphs and charts so I've not done much of it. This person gave me this:



and they created it by doing a ton of redundant things, like creating 1 access query per month, then using a union query to join them all, seen here:



they've asked me to automate everything so the business owner only presses 1 button and sees it all happen. that's not a problem, but this person wants me to retain all the data in an excel sheet so he can create a graph from it. So, my question is, are graphs created from raw source data like this?



or can they be created from the summary "group by" data that can be seen in the first image above, which is an access report? thanks! (all this data is dumped from the mobile app called SQUARE.
 

Attachments

  • source_data_from_square.jpg
    source_data_from_square.jpg
    74.2 KB · Views: 461
  • monthly_queries_to_union.jpg
    monthly_queries_to_union.jpg
    28.1 KB · Views: 420
  • group_by_data_in_report.jpg
    group_by_data_in_report.jpg
    69.2 KB · Views: 443
  • sample raw data file.xls
    29 KB · Views: 291

GinaWhipp

AWF VIP
Local time
Today, 11:34
Joined
Jun 21, 2011
Messages
5,901
Hmm, so the suggestion I posted yesterday appears to be gone. Or am I missing something?
 

Jon

Access World Site Owner
Staff member
Local time
Today, 15:34
Joined
Sep 28, 1999
Messages
7,312
@GinaWhipp, you posted during the migration. Here is your post for you:

Hmm, well the first thing is create one query to do this and eliminate the UNION query. When setting up the query make sure to include columns for parameter look-up's, i.e. Month, Year and maybe Location.

Now create an Excel workbook, create two tabs one for the Chart and the other for the Raw Data. Now on the Raw Data tab put some dummy raw data and then put a Chart on the other tab using the Raw Data to feed your Chart. Once you have your Chart set up delete all the data on the raw data and save the Excel Workbook. (Oh, and remember you need to plan for maximum rows on the Raw Data tab. You can use a formula if you like.)

And finally, in Access create a Form that allows Users to select parameters. Then add a button to use Excel Automation export to the Raw Data tab in the saved Excel workbook. You can look here for some code tips...
https://btabdevelopment.com/export-a...fic-worksheet/

Side note, one of the reason I suggest using a SELECT query is because UNION queries get notorious slow with the adding of more data. Also, you have to update them every month, in your example. By using a SELECT query data just gets added based on User additions.
 
Last edited:

Jon

Access World Site Owner
Staff member
Local time
Today, 15:34
Joined
Sep 28, 1999
Messages
7,312
You're welcome. Saves you typing it out again!
 

Users who are viewing this thread

Top Bottom