Creating a Stacked Column Chart

L

Lkwdmntr

Registered User.
Local time
Yesterday, 22:22
Joined
Jul 10, 2019
Messages
287
I have a query that has the "UserId", "WeekNumber", "Action", "Planned", and "Actual" as columns. I need a chart for each action that compares the "Actual"(Days Performed) against the "Planned"(Days Performed). The Y axis would have 1 - 7 for Days of the week and the X axis would have the Week numbers 1-12.

I looked at some tutorials, but they weren't very helpful. Again, I need a chart for EACH action.
 
Provide sample data. I presume Action is a text field.

Show a mock up of desired graph.

Do charts need to be generated for each UserID/Action?

Bind form or report to table. Bind textboxes to UserID and Action fields. Set graph RowSource like:

SELECT WeekNumber, Planned, Actual FROM Table1 WHERE UserID=[tbxUser] AND Action=[tbxAction];
 
Last edited:
I cut down my database so it only has what is needed to make the charts.

I'm using the planned and actual query and am hoping to get a chart for each action.

I made a quick sample chart in Excel to show you what I'm looking for. The text on the X-axis should only have the week numbers, but you get the idea.

The database is still over 2MB and can't seem to get it any smaller.

Here is a link to it in Google drive.

 
I know that people are skittish about using a link to access the database. I was finally able to condense everything down to under 2MB in a zip file. I have just the queries and tables that I am using to pull the data. I made some progress in organizing the data to get it ready to make the charts, but need a little help.

There are some actions that have two indexes, the one with the lower index is the "StartTimeAction", which make the client accountable for starting the action on time and also completing the action. For these types of actions, two charts need to be produced, "Start Time" and "Activity".

My problem is getting the number of activity points combined to the action with the same name but the higher index.

Let's do an example to make things clearer. If you open "PlannedandActualWSTCAC" (stands for weekly start-time challenges activity) you'll see "Exercise Routine" with the index of 217. I need the "PlannedAC" and the "ActualAC" to be combined with the "Exercise Routine" in the other query "PlannedandActual", where the index is 331 and of course the UserID and WeekNumber need to match up as well.

I know this is a lot to understand, but I think the more organized the data, the easier the charts will be to make.

Thank god I am dealing with intelligent people. Please don't hesitate to ask me any questions that may help things be better understood.
 

Attachments

I get an error trying to extract file.

Did you try suggestion in post 2?
 
Did you not see that I posted it for you, or was something wrong with it?
 
I download that one as well as the GoogleDrive file. I don't find any object named PlannedandActualWSTCAC.

I find it hard to believe all of these tables and queries and forms and reports are necessary to produce the desired graph.

Dang, now this db looks so familiar.
 
This Full database does have a lot in it and it does an awful lot of things. The cut-down version only has a couple tables and a few queries. I just double-checked the DB and everything is there that is needed to create the charts.

Yes, June7 I'm quite sure you remember the first version, you should be happy with the cut down version. Its what you were trying to get me to do all along.

Those two queries need to be combined into one, only then can we get started on the charts, which I have never done before. I tinkered a little in Excel, but never in Access. That Excel file I sent earlier has a rough draft of what is needed.

Please know how very grateful I am that you guys are even entertaining this.
 
Last edited:
Micron, Thanks for posting that, but I made a lot of changes since that version. Hopefully for the better. The cut-down version only has what is needed for creating the graphs.

I still would love to know how you condensed that one version of the database. It was over 13MB. I got this one down to a little over 4MB and after zipping, it only cut it in half. I barely made the 2MB requirement.
 
I can't extract the file provided in post 7.
 
That Excel chart does not show stacked column. Access version will not have multi-line x-axis labels.

I tried deleting data to get file small enough but that didn't work so I imported everything to a new file. See if this graph gets you on right track. I forgot to change chart title to Body Weight.
 

Attachments

Pretty sure I got the file from post 4 link. It didn't look any different than the one from the other day and June7 seems to have verified that. I have also stated that your issue has been images stored in a table and I have posted which one that is. Remove them and you can get the file size down.

Actually, your problem is also one of having too many objects that appear to be doing basically the same thing, but that's another story.
 
The chart is pretty much what I need. It does only need to show the 7 days, not 8. I'm a little worried about the issue of combining the PlannedAC and the ActualAC values to the Planned and Actual values in the other query. The final query should have the two values combined for the action with the higher index number. So that example with the Exercise Routine, for week 3 the Planned value should be 7((PlannedAC = 4)+(Planned = 3)) and the Actual value should be 5((PlannedAC = 2)+(Planned = 3)).

Also, if there is no data for the first 3 weeks, will the chart show the empty weeks(it needs to). It should always have 7 days and 12 weeks.

I tried to see how you made the chart and can't seem to figure it out.

Thanks a ton for the work thus far.
 
What is it you do not understand about creating charts? What version of Access do you have? I use Access2010 and the MSGraph control. I know nothing about ModernCharts tool in Access2016.

Can set the Y-axis scale Min and Max properties to 1 and 7.

Even if there is no data, Y-axis scale will always show numbers 1 through 7. However, for this type of graph, missing weeks likely will not display. I did not test.

How do you suggest summing data when Actions are not the same in both datasets?
 
I am using 2016, so creating the graph is going to be different on my end.

The actions are the same. In one query they are "Actions" and the other they are "StartTime Actions". I was wondering if there was a way to do a function or something in SQL. Something like- If Action = StartTimeAction and UserID=UserID and WeekNumber= WeekNumber. Then Add PlannedAC to Planned where the index of the action is greater, and Add ActualAC to Actual where the index of the action is greater.

I've made functions on MySQL, but never in access. Not sure how to go about it.

Another thought is to try adding it right within one of the queries. Not sure how that would go either, just shooting out ideas.
 
Yes, I was considering a compound join but then I found that Body Weight is not in both. So an INNER JOIN resulted in no records. If there are actions in each not also in the other, this will be quite difficult.
 
You must log in or register to reply here.

Similar threads

T
I need to show Negative numbers as a Zero for Forecast Chart
Replies
2
Views
90
cheekybuddha
cheekybuddha
B
Set a pie chart with values from a listbox access vba
Replies
3
Views
172
b24 sofwtares
B
D
Table Search and Total
Replies
18
Views
316
DDJKSA
D
brucemc777
Solved HOW??? An HTML string stored in a long text table field with breaks for variables to be used in boilerplate eMails...
Replies
10
Views
368
brucemc777
brucemc777
K
Filtering in data sheet view on combo box with multiple columns?
Replies
1
Views
614
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom