Crystal, I've been all over the internet trying to find ways of dynamically creating charts in Access. There's lots of info on Excel charting but that's not what I've been tasked to do. This chart I am building needs to be pulled from a query and the headings will change based on some of the parameters. This is a great tutorial! Downloaded your sample database. Thanks a bunch.
I have a form in Access where I was called upon to replicate a Delphi form with some exactitude. It features two graphs: the first a tall bar graph with approx. 500 values, and the second a zoomed-in subset of 60 values from the first. I effected these by overlaying transparently-backed graph elements atop one another. Whereas the first, "full" graph features a static bitmap for its grid and numbering, on the second graph these elements must be replicated. The "axis border" and "major tick marks" (at tens) are actually a bar graph in-and-of-themselves - not a "true" border and tick marks. And since they represent the X-axis of a bar graph, they carry NO "Minimum" and "Maximum" value. I *have* created a column graph that is invisible save for its modifiable Y-axis scale numbering - but I cannot properly sync it to the graph data itself, which essentially scrolls onto and off of the screen. (I couldn't even seem to shift the chart upwards on the form programatically.) Am I going about this whole thing wrong? Is there a simpler method? Am I neck-deep in a madhouse of my own design? Not looking for anyone to do this *for* me, I just feel as though I've lost my way to the finish line after 1497 meters... Thanks a bunch in advance!
hi Anthony, thanks for your comment. Note quite sure what your question is ... for scaling the Y-axis, the easy way is to use the format code. Each comma on the end divides by 10^3. For scaling the X-axis, I modify the BaseUnit and MajorUnit, which you can also do for the Y-axis instead of format. The Y-axis also has BaseUnitIsAuto. Sounds like a neat chart ... I'd love to see a screen shot if you can post one.
Here is an image showing the "original" at top and my Access approximation at bottom: ibb.co/BTQ5QG0 . As you can see, everything but the zoomed image at right is fairly accurately reproduced (though the implementation of the "crosshairs" pointer as one crosses the "full" frequency graph [at 49Khz here] gets rather glitchy). The zoomed graph itself is also pretty much spot-on. It's just that numbering that will not cooperate no matter what I think to do. Again, the thick black structure representing the major ("decade") lines on this graph IS ACTUALLY A BAR GRAPH ITSELF. Although it seems to "move" as I run my mouse up/down the length of the frequency image, it is really just reflecting updated values from the table as I scan. Although I have gotten the numbers to CHANGE (they are generally within 10Khz of what the should be), I have not succeeded in making them appear to SCROLL up and down in sync with the (apparent) movement of the graph. You will notice that on the "original" zoomed graph, the top number ("50") is partially scrolled off the screen, whereas on my copy, the top number (also "50") is stationary (but as you scroll down, all the displayed numbers increase). WHEW! Are you tired? I sure am! LOL Thanks for listening! I will surely continue my explorations along the lines you've indicated.
I am trying something new now that I believe will work: I have created a "flip book" of sorts consisting of 10 images progressively shifting down the screen. By alternating the visibility as I scroll down, I should be able to effect a rolling image. Will let you know.
@@ADeCosa59 looks impressive. Another way to show a chart is to use Unicode block characters in a query. I have a video about that here: ua-cam.com/video/TRABcOwRhaQ/v-deo.html
@@LearnAccessByCrystal I hadn't thought of Unicode characters to create text-based bar graphs. That's a head-slapper there! I tried the 10-position "flipbook" and it WORKED, producing a image that scrolled up & down as you moved the mouse. But again, I could not seem to properly sync it to the scrolling of the graph itself. I have yet another idea I'd like to try: having 500 separate 28×60 element images of the numbers and tick marks and creating an SWF that I can flip through, forward and back, as a scroll up/down. That would seem to give me much more accurate registration.
you're welcome, Ron Netgrazer . Thank you for the kind words. The code for manipulating graphs using VBA is posted here: www.msaccessgurus.com/Code/_CodeDocumenter_GraphExample_Crystal_140812_9p_accdb_140928.HTM ~ Crystal
LearnAccessByCrystal Thank you for posting this video and the code!! It was exactly the help I needed for my MS Access project! If I have questions about how to manipulate this code for my Queries, do I post it here or is there a better forum?
I created one chart with time hh:nn:as values and it is working fine. But, when I am creating same type of chart on new form with same query. I am getting 1 for all data points. Kindly help me find what I am missing here.
hi Vijay, there is a little trick to getting more granularity with dates and times ... in the query you use for the RowSource of the chart, convert the date data to Double Precision (you can use the CDbl function). Then in the chart design, format the axis to show date and/or time the way you want. This only works, so far, with classic charts, not the modern ones.
Hello Crystal! Thanks for sharing such amazing video! I am using the graph example from the web you suggested. But I have 2 questions. 1)I know that the VBA code automatically update and rewrite each query by the 'topic' you selected on the menu form, but I wonder before that, do we need a query to be there? 2) Do we need a graph initially to be there to get the code running? If so, what will be the raw source of the graph? is it from the query we created in my 1st question? 3) its just a combination of two of my previous questions: Do we need a query, a graph prior to the menu form to get the vba in the menu form running? Thank you in advance and I've encountered some issues using this as an template for graphing my data. it always pops out this error message says: the select statement was wrong. Thanks!
+Yukitokase > topic ..., do we need a query to be there? you want to create a query to make the graph from -- and that determines what you will need. I included "Topic" because I thought most graph menus would need a category of some kind, in addition to a date range, to drill down to > 2. Do we need a graph initially to be there to get the code running? yes, you need to create the graph object that VBA can manipulate > says: the select statement was wrong ** debug.print *** debug.print sSQL --> this prints a copy of the SQL statement to the debug window (CTRL-G) After you execute your code, open the Debug window CTRL-G to Goto the debuG window -- look at the SQL statement If the SQL statement has an error 1. Make a new query (design view) 2. choose View, SQL from the menu (or SQL from the toolbar, first icon) (or right-click on a blank area in the query design and choose --> SQL View) 3. cut the SQL statement from the debug window (select, CTRL-X) 4. paste into the SQL window of the Query (CTRL-V) 5. run ! from the SQL window -- Access will tell you where the problem is in the SQL warm regards, crystal ~ have an awesome day ~
Hi Karl, thanks for your question. While I don't have a tutorial showing how to create the graph menu specifically, several of my videos go into form design. Menu forms are typically unbound (no Record Source) with Default View set to Single Form, Record Selectors=No, Navigation buttons=No. Beyond that, you need to know how to create controls and if the control has a list (such as a combo box or listbox), set the properties needed. Here are 2 videos that can help you: Create a Form in Microsoft Access ua-cam.com/video/norAB9QvIls/v-deo.html Combo Box properties in Microsoft Access (most applicable to list boxes too) ua-cam.com/video/2y85oDWaSh0/v-deo.html
thanks, James! For those who don't know how to make the initial chart, here is a video for that: ua-cam.com/video/YhgNX6BWWmk/v-deo.html ~ Crystal ~ have an awesome day ~
This looks really quite professional! Thank you. Unfortunately, the code repositories would not let me download the code sample. MVP site says File not found and the other site says I need to be a member! Is there any other way to get this code
@@LearnAccessByCrystal THANK YOU ever so much for this! You are very correct in your observation that "charts" are NOT very well documented. I used SCADA software at work which was largely undocumented - then I had occasion to meet one of the programmers who'd created the software, and he passed along many invaluable tips and tricks. Is that kind of like what happened here? It seems it would take FOREVER to learn all this just by trial and error! The version you have linked to here is for "9p" while the version cited in the video is "10p". Any qualitative differences we should be aware of? (Does "10p" support newer versions of Access?) Again, thanks for all this. I am so glad I found it and can't wait to immerse myself in it!
@@anthonydecosa2389, you're welcome and thank you! great to hear! I hope you build an amazing tool. lol, 9p and 10p are times ... 9:00 pm and 10:00 pm ... to keep track of versions . Trial and error is one way I've figured things out, but more it is because of websites by Excel greats Jon Peltier, Ron de Bruin, and others, reading forums, looking up Help on Microsoft's site ... using the object browser on the Microsoft Graph #.0 Object Library (BEST place to start) ... and a strong graphing background. Love charts! I've just spent the past couple weeks on a custom charting and dashboard tool in Access -- perhaps it is time to update my sample with a little more dazzle too ... when I get another week or two to do it. I connect and teach while you build an application, or just answer questions and get you out of snags ... if interested, or you know someone who is, take a look at msaccessgurus.com/ --there is also a lot of free code, tools, and a list of my UA-cam videos if you are looking for something in particular or just want to browse. Good luck to you!
The download has source code that you can study and modify. This is not a beginner topic. To be able to do something like this, you must develop proficiency with Access first. Watch my "Learn Access" playlist, and follow links in each video description. Here is also a free book: www.AccessMVP.com/strive4peace
Hi I really need to create a database in ms access, which can generate graph base on search, same this video, but different parameter, can you help me, can I have your email, so I can send to you personally my query, thank you so much, god bless
+Renalyn Flores Thank you. You may email me at "strive4peace2011 @ yahoo . com " (remove all the spaces between the quote marks -- and then remove the quote marks. I would be happy to teach you how to customize this example for yourself ~
Sorry for my thumbs down - should be thumbs up. The strive4peace link in the reply you gave to Chambers works great. The Yahoo groups link not so good..
Crystal, I've been all over the internet trying to find ways of dynamically creating charts in Access. There's lots of info on Excel charting but that's not what I've been tasked to do.
This chart I am building needs to be pulled from a query and the headings will change based on some of the parameters. This is a great tutorial! Downloaded your sample database. Thanks a bunch.
thank you, StavrosTO -- and you're welcome ~
Way cool, Crystal!!!
thanks, Mike! ~ Crystal
I have a form in Access where I was called upon to replicate a Delphi form with some exactitude. It features two graphs: the first a tall bar graph with approx. 500 values, and the second a zoomed-in subset of 60 values from the first. I effected these by overlaying transparently-backed graph elements atop one another.
Whereas the first, "full" graph features a static bitmap for its grid and numbering, on the second graph these elements must be replicated. The "axis border" and "major tick marks" (at tens) are actually a bar graph in-and-of-themselves - not a "true" border and tick marks. And since they represent the X-axis of a bar graph, they carry NO "Minimum" and "Maximum" value. I *have* created a column graph that is invisible save for its modifiable Y-axis scale numbering - but I cannot properly sync it to the graph data itself, which essentially scrolls onto and off of the screen. (I couldn't even seem to shift the chart upwards on the form programatically.)
Am I going about this whole thing wrong? Is there a simpler method? Am I neck-deep in a madhouse of my own design?
Not looking for anyone to do this *for* me, I just feel as though I've lost my way to the finish line after 1497 meters...
Thanks a bunch in advance!
hi Anthony, thanks for your comment. Note quite sure what your question is ... for scaling the Y-axis, the easy way is to use the format code. Each comma on the end divides by 10^3. For scaling the X-axis, I modify the BaseUnit and MajorUnit, which you can also do for the Y-axis instead of format. The Y-axis also has BaseUnitIsAuto.
Sounds like a neat chart ... I'd love to see a screen shot if you can post one.
Here is an image showing the "original" at top and my Access approximation at bottom: ibb.co/BTQ5QG0 . As you can see, everything but the zoomed image at right is fairly accurately reproduced (though the implementation of the "crosshairs" pointer as one crosses the "full" frequency graph [at 49Khz here] gets rather glitchy).
The zoomed graph itself is also pretty much spot-on. It's just that numbering that will not cooperate no matter what I think to do. Again, the thick black structure representing the major ("decade") lines on this graph IS ACTUALLY A BAR GRAPH ITSELF. Although it seems to "move" as I run my mouse up/down the length of the frequency image, it is really just reflecting updated values from the table as I scan. Although I have gotten the numbers to CHANGE (they are generally within 10Khz of what the should be), I have not succeeded in making them appear to SCROLL up and down in sync with the (apparent) movement of the graph. You will notice that on the "original" zoomed graph, the top number ("50") is partially scrolled off the screen, whereas on my copy, the top number (also "50") is stationary (but as you scroll down, all the displayed numbers increase).
WHEW! Are you tired? I sure am! LOL
Thanks for listening! I will surely continue my explorations along the lines you've indicated.
I am trying something new now that I believe will work: I have created a "flip book" of sorts consisting of 10 images progressively shifting down the screen. By alternating the visibility as I scroll down, I should be able to effect a rolling image. Will let you know.
@@ADeCosa59 looks impressive. Another way to show a chart is to use Unicode block characters in a query. I have a video about that here: ua-cam.com/video/TRABcOwRhaQ/v-deo.html
@@LearnAccessByCrystal I hadn't thought of Unicode characters to create text-based bar graphs. That's a head-slapper there!
I tried the 10-position "flipbook" and it WORKED, producing a image that scrolled up & down as you moved the mouse. But again, I could not seem to properly sync it to the scrolling of the graph itself.
I have yet another idea I'd like to try: having 500 separate 28×60 element images of the numbers and tick marks and creating an SWF that I can flip through, forward and back, as a scroll up/down. That would seem to give me much more accurate registration.
Thank you so much! This is fantastic on several levels at the same time.
you're welcome, Ron Netgrazer . Thank you for the kind words. The code for manipulating graphs using VBA is posted here: www.msaccessgurus.com/Code/_CodeDocumenter_GraphExample_Crystal_140812_9p_accdb_140928.HTM ~ Crystal
LearnAccessByCrystal Thank you for posting this video and the code!! It was exactly the help I needed for my MS Access project! If I have questions about how to manipulate this code for my Queries, do I post it here or is there a better forum?
+Hawkfly thank you, Hawkfly and you're welcome. You may post questions here.
Great job! I look forward to more of your materials/videos. Thanks
thank you, John!
I created one chart with time hh:nn:as values and it is working fine. But, when I am creating same type of chart on new form with same query. I am getting 1 for all data points. Kindly help me find what I am missing here.
hi Vijay, there is a little trick to getting more granularity with dates and times ... in the query you use for the RowSource of the chart, convert the date data to Double Precision (you can use the CDbl function). Then in the chart design, format the axis to show date and/or time the way you want. This only works, so far, with classic charts, not the modern ones.
Hello Crystal! Thanks for sharing such amazing video! I am using the graph example from the web you suggested. But I have 2 questions. 1)I know that the VBA code automatically update and rewrite each query by the 'topic' you selected on the menu form, but I wonder before that, do we need a query to be there? 2) Do we need a graph initially to be there to get the code running? If so, what will be the raw source of the graph? is it from the query we created in my 1st question? 3) its just a combination of two of my previous questions: Do we need a query, a graph prior to the menu form to get the vba in the menu form running? Thank you in advance and I've encountered some issues using this as an template for graphing my data. it always pops out this error message says: the select statement was wrong. Thanks!
+Yukitokase
> topic ..., do we need a query to be there?
you want to create a query to make the graph from -- and that determines what you will need. I included "Topic" because I thought most graph menus would need a category of some kind, in addition to a date range, to drill down to
> 2. Do we need a graph initially to be there to get the code running?
yes, you need to create the graph object that VBA can manipulate
> says: the select statement was wrong
** debug.print ***
debug.print sSQL
--> this prints a copy of the SQL statement to the debug window (CTRL-G)
After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement
If the SQL statement has an error
1. Make a new query (design view)
2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
(or right-click on a blank area in the query design and choose --> SQL View)
3. cut the SQL statement from the debug window
(select, CTRL-X)
4. paste into the SQL window of the Query
(CTRL-V)
5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL
warm regards,
crystal
~ have an awesome day ~
Do you have a video showing how to build the Graph Menu
Hi Karl, thanks for your question. While I don't have a tutorial showing how to create the graph menu specifically, several of my videos go into form design.
Menu forms are typically unbound (no Record Source) with Default View set to Single Form, Record Selectors=No, Navigation buttons=No.
Beyond that, you need to know how to create controls and if the control has a list (such as a combo box or listbox), set the properties needed. Here are 2 videos that can help you:
Create a Form in Microsoft Access
ua-cam.com/video/norAB9QvIls/v-deo.html
Combo Box properties in Microsoft Access (most applicable to list boxes too)
ua-cam.com/video/2y85oDWaSh0/v-deo.html
this is a great video!
thanks, James! For those who don't know how to make the initial chart, here is a video for that: ua-cam.com/video/YhgNX6BWWmk/v-deo.html ~ Crystal ~ have an awesome day ~
Really nice work, well explained. Thanks! Cats rule... ;-)
thank you ... you must have heard a meow? That was my assistant ;) ~
This looks really quite professional! Thank you. Unfortunately, the code repositories would not let me download the code sample. MVP site says File not found and the other site says I need to be a member! Is there any other way to get this code
thank you! I corrected the link in the video description. You can download the sample database from www.accessmvp.com/strive4peace/Access_Graphs.htm
@@LearnAccessByCrystal THANK YOU ever so much for this! You are very correct in your observation that "charts" are NOT very well documented. I used SCADA software at work which was largely undocumented - then I had occasion to meet one of the programmers who'd created the software, and he passed along many invaluable tips and tricks. Is that kind of like what happened here? It seems it would take FOREVER to learn all this just by trial and error!
The version you have linked to here is for "9p" while the version cited in the video is "10p". Any qualitative differences we should be aware of? (Does "10p" support newer versions of Access?)
Again, thanks for all this. I am so glad I found it and can't wait to immerse myself in it!
@@anthonydecosa2389, you're welcome and thank you! great to hear! I hope you build an amazing tool. lol, 9p and 10p are times ... 9:00 pm and 10:00 pm ... to keep track of versions
. Trial and error is one way I've figured things out, but more it is because of websites by Excel greats Jon Peltier, Ron de Bruin, and others, reading forums, looking up Help on Microsoft's site ... using the object browser on the Microsoft Graph #.0 Object Library (BEST place to start) ... and a strong graphing background. Love charts! I've just spent the past couple weeks on a custom charting and dashboard tool in Access -- perhaps it is time to update my sample with a little more dazzle too ... when I get another week or two to do it. I connect and teach while you build an application, or just answer questions and get you out of snags ... if interested, or you know someone who is, take a look at msaccessgurus.com/ --there is also a lot of free code, tools, and a list of my UA-cam videos if you are looking for something in particular or just want to browse. Good luck to you!
Can you teach me how to create this pls :(
can teach me step by step?
The download has source code that you can study and modify. This is not a beginner topic. To be able to do something like this, you must develop proficiency with Access first. Watch my "Learn Access" playlist, and follow links in each video description. Here is also a free book: www.AccessMVP.com/strive4peace
Hi I really need to create a database in ms access, which can generate graph base on search, same this video, but different parameter, can you help me, can I have your email, so I can send to you personally my query, thank you so much, god bless
+Renalyn Flores Thank you. You may email me at "strive4peace2011 @ yahoo . com " (remove all the spaces between the quote marks -- and then remove the quote marks. I would be happy to teach you how to customize this example for yourself ~
Sorry for my thumbs down - should be thumbs up. The strive4peace link in the reply you gave to Chambers works great. The Yahoo groups link not so good..
sorry for the 'bad' link, Jim! The domain just switched to Linux and now UPPER-lower case matters! Glad you like it, thanks