creating a graph from access "group by" data

[vba_php]

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.

 

[GinaWhipp]

Hmm, so the suggestion I posted yesterday appears to be gone. Or am I missing something?

 

[Jon]

@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.

 

[GinaWhipp]

Thanks Jon!

 

[Jon]

You're welcome. Saves you typing it out again!