UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Trying To Do A Chart That Looks Like My Excel Chart, Access 2016    
 
   
wornout
post Apr 19 2017, 11:57 PM
Post#1



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


Hi I have a perfect chart in excel but now I have put all data and some more in a database and I can not replicate the chart in access. I know charts are hard to do in access but I thought someone could help I have tried and tried but can not get the columns to be the never, rarely etc part and the count up the side I can get the questions on the bottom
Hoping I have attached both excel and database
Attached File(s)
Attached File  Safety_SurveyV1___Shortcut.zip ( 833bytes )Number of downloads: 10
Attached File  help_database.zip ( 29.94K )Number of downloads: 11
 
Go to the top of the page
 
RJD
post Apr 20 2017, 12:39 AM
Post#2


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


Hi: Looks like you captured only the shortcut file in the first zip rather than the Excel file. Did you mean to do that? No chart there.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 20 2017, 01:05 AM
Post#3



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


whoops no here you go
Attached File(s)
Attached File  Safety_SurveyV2.zip ( 48.38K )Number of downloads: 14
 
Go to the top of the page
 
RJD
post Apr 20 2017, 01:10 AM
Post#4


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


Thanks. Shutting down for the night (WAY late, no, early a.m. hours), so will look in the morning. But now others can look as well, with the Excel file posted.

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 20 2017, 01:27 AM
Post#5



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


thank you night our night time in new zealand is just beginning its only 6.30pm
Go to the top of the page
 
RJD
post Apr 20 2017, 02:41 PM
Post#6


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


Hi: I took a look at this today - and have some suggestions. While the chart in Excel contains all the basic information (although I do not think it is the best for understanding the information), I would approach the display of the information a bit differently in Access. First, I would orient the survey items vertically rather than horizontally. Placing them across the bottom of the chart as you have done (so that the reader must read vertically) makes them, IMHO, hard to read, whereas vertical listing, with horizontal, left to right reading, is a natural and easy way for users to read the items. I would also add an overall response frequency and calculate a mean response value. This mean value allows you to better compare items and pick out those with lower scores for more management attention.

I handled the bars differently than in your chart example, making them horizontal using the String function for each bar (rather than using an Access chart) and used your approximate hue values from the Excel spreadsheet. You may have to adjust the multiplier based on how many responses you might get, and may have to use a calculated multiplier based on the maximum count in a response category.

In all, I think this conveys the information a bit better.

There are also other ways to analyze the information, such as plotting the mean scores to highlight more important problem areas, or analyzing the "Never" counts for items (or responses that management deems of special concern). The question you must ask yourself is what decisions the results will drive, and display the information for best clarity in that regard.

I realize that I did not address creating the example chart in Access, but consider the report in the revised db and see what you think.

(You might also want to consider Factor Analysis as well sometime in the future, to winnow down the number of essentially identical list items, although that might be a big task - something for a graduate thesis, perhaps!)

HTH
Joe
Attached File(s)
Attached File  help_database_Rev1.zip ( 33.63K )Number of downloads: 8
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 20 2017, 04:20 PM
Post#7



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


I like what you have done and yes it does look better I hope they will accept that they are so used to excel charts.
But they need to know how many of each response to each question not just the total/mean.
I am trying to work out the bars could we have at the beginning of the bar the name and count ie Never 6
you have =String(Nz([Never],0)*2,"▬")
how can I change that
so far have come up with this
="Never" & [Never] & String(Nz([Never],0)*2,"▬")
This post has been edited by wornout: Apr 20 2017, 04:26 PM
Go to the top of the page
 
RJD
post Apr 20 2017, 04:36 PM
Post#8


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


Take a look at the revision attached. Really very simple to add the numbers and text - it's just a report.

No need to mess with the bar (String function) to show the numbers.

And no need to put everything in one line. If you do that, due to the different lengths of the descriptions, the bars will start at different places.

HTH
Joe
Attached File(s)
Attached File  help_database_Rev2.zip ( 34.35K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 20 2017, 05:10 PM
Post#9



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


awesome i tried to do that but it took up 21 pages in pdf lol
now for them to approve it

thank you so much

Go to the top of the page
 
RJD
post Apr 20 2017, 05:23 PM
Post#10


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Always happy to assist.

Good luck with your project ... and getting management approval for the report!

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 20 2017, 07:06 PM
Post#11



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


no they dont like it only because they copy and paste the chart to a powerpoint presentation and the chart fits on one slide. I am going to try a play around with it but dont like my odds. They would like me to keep the report but do something they can put on a slide thats visual
Go to the top of the page
 
RJD
post Apr 20 2017, 10:27 PM
Post#12


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


You're probably going to be better off using Excel for this. It just has more options for displaying the chart.

However, I started a chart for you to work on (see rptChart_2 in the attached db). You will notice that I had to reformat the table using a UNION query to put the data in a condition to build this.

See if this gets you along a solution path, or convinces you to use Excel (or another graphic software package) instead.

This is beyond the scope of your question, but sounds like your management may need some information on how to best display quantitative information for best effect to highlight important results. I recommend Stephen Few and Edward Tufte books for that, especially Few's "Show Me the Numbers" and "Now You See It". Excellent books on the subject.

I can't really see how the current chart you are using would make the relevant results jump out at the decision-makers. But, then that's just me ...

HTH
Joe


Attached File(s)
Attached File  help_database_Rev3.zip ( 39.85K )Number of downloads: 11
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 21 2017, 10:17 PM
Post#13



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


That is very good thank you they are happy with that woohoo.gif
Go to the top of the page
 
RJD
post Apr 21 2017, 10:23 PM
Post#14


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Best of luck in your endeavors.

Regards,
Joe

from phone

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 22 2017, 06:14 PM
Post#15



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


after all that hard work I was playing around to see if report would fit on a slide in power point when I discovered that it is easier to make one in power point and copy and paste the data into the chart table in power point.
Would be nice if I could automate this process but never mind
Go to the top of the page
 
RJD
post Apr 23 2017, 10:42 AM
Post#16


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


Hi again: Not sure how you are doing the chart in PP or how you are transferring the data, but let us know if we can provide further assistance.

I think the key to all this is, of course, a presentation to management that highlights the safety emphasis issues, where shortcoming exist, and where attention needs to be focused. And some sort of indicator metric (with, say, the top 5 or 10 areas of concern) might be more telling than a mass of bar charts to get lost in. But again, as I said, this is just me and how I look at things, and beyond the scope of your technical question ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 23 2017, 04:50 PM
Post#17



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


I am tring to use this bit of code and in the file path it debugs and say unrecognizable file format the code from the sample data base is a mdb
CODE
Public Sub Assign_Tag(Slide As PowerPoint.Slide)
    Dim i As Long
    
    'first delete any existing tags on Slide
    For i = 1 To Slide.Tags.Count
        Slide.Tags.Delete (i)
    Next i
    Slide.Tags.Add "DBPath", ActivePresentation.Path & "\DatabaseV6.accdb"  'database path
    Slide.Tags.Add "Table", "ServeyQuestions"  'table name
End Sub


It actually debugs on this bit of code
CODE
'---------------------------------
'Open Database and Recordset object
'---------------------------------
Private Sub OpenDBAndRecordset(Slide As PowerPoint.Slide)

    Dim strTable   As String
     'Open DAO database connection
    Set db = OpenDatabase(Slide.Tags("DBPath"))
    
    'Get Table Name
    strTable = Slide.Tags("Table")
    
    'Open Table recordset
    Set rs = db.OpenRecordset("Select * from " & strTable)
    
End Sub

I have office 365
This post has been edited by wornout: Apr 23 2017, 05:03 PM
Go to the top of the page
 
RJD
post Apr 23 2017, 07:04 PM
Post#18


UtterAccess VIP
Posts: 7,691
Joined: 25-October 10
From: Gulf South USA


Hi: I am a bit lost in what you are doing - and feel beyond my knowledge base. So, I'll put out a call to the VIPs and see if anyone can assist with this.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
wornout
post Apr 23 2017, 11:20 PM
Post#19



Posts: 947
Joined: 17-November 13
From: Orewa New Zealand


as the coding is in the power point I think maybe I should try there as well
Go to the top of the page
 
dflak
post Apr 24 2017, 08:36 AM
Post#20


Utter Access VIP
Posts: 5,984
Joined: 22-June 04
From: North Carolina


I do charts from Excel to PowerPoint on an "industrial" scale. So I developed a technique where you fill out a table in excel that tells the program, go to this excel sheet, copy this range or chart, paste it on this powerpoint sheet, position it and resize it with the dimensions in the table. FYI: when I say range, named dynamic ranges will also work.

Since this is already in my toolbox, I'll post it here. Maybe it will give you some fresh ideas.


Attached File(s)
Attached File  PPT_Sample.zip ( 75.04K )Number of downloads: 8
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2017 - 12:52 AM