By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

How to show my production percentage in speedometer or gauge chart in ms access

P: 66
I am developing a dashboard where I would like to show some speedometer kind of charts that shows total percentage achieved.

I have searched and found some ideas like showing it through combination of pie chart and doughnut chart. But In that the back ground doesn't looks perfect for my dashboard.

So I would like to show some speedometer kind of background with needle shows the percentage. I have also got a database as an example as i have expected but if I change the size of that speedometer or background image from the below link. I am not getting the correct data in it.

http://www.utteraccess.com/forum/ind...wtopic=1979324

Please help me out in this, attached the database only with the required form for your reference.

Thanks
Johny
Attached Files
File Type: zip Database5.zip (192.3 KB, 550 views)
Jan 5 '15 #1

✓ answered by twinnyfo

This is actually quite involved.

Some principles to start with are that you need to determine the radius of your needle. This will be based on about half the width of your speedometer.

Then, based on the image you are using, you need to find the center point of the needle. Based on the radius of the needle, and the percentage complete from 0 to 100% (assuming 180 degrees of motion, would translate to 0 - 180 degrees). Then, translate those degrees into radians (because that is how Access calculates), use the sine and cosine to determine the location of the end point. Based on how far along the speedometer is, you change the slant of the line and resize the object.

I remember when I first tackles this fun little project that it took me several hours to get all aspects of the clock to work correctly, as the center point of the line must remain constant, but the sizing handles and slant moves based on the location of the needle along the clock face.

Because of the complexity of this I may opt to write an article in this forum and post a link to that article on this thread. Right now, I just don't have the time to give all the details on how to get where you want to be--and I don't want to just leave you hanging.

Give mea few days to try to put something together that would be meaningful and less rushed.

Thanks!

Share this Question
Share on Google+
14 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
Johny,

I can't access ZIP files at work, and we try to discourage posters from posting their projects unless we ask for them.

You r first challenge will be the chart itself, since MS Access does not even have the capability to add a circle shape. So, my first recommendation will be for you to create the image of your speedometer background. Use whatever software you want, and as long as it is saved in a format that Access can use (.png or .jpg should work fine).

Then, you just need to figure out what the "needle" will be pointing to and the scale of the chart. You will have to determine if your scale goes to "0-100 percent" or some other values (which will have to correspond to the design of your speedometer background).

The needle itself is merely a line object, but will take a little bit of mathematics and geometry to calculate and display properly. Because I was interested in just such displays of line objects, I experimented with this and created a working analog clock in MS Access which displays a second, minute and hour hand.
Jan 5 '15 #2

P: 66
I am sorry, I can attach the clock which i have already designed and selected the needle as well, but not sure how to make it work.

Please find attached both the files for your review. Please advise on the math part that will make it work.
Attached Images
File Type: png 23.png (8.2 KB, 3848 views)
File Type: jpg SPM.jpg (45.8 KB, 4361 views)
Jan 5 '15 #3

twinnyfo
Expert Mod 2.5K+
P: 3,482
Johny,

Sorry for the late response--been very busy at work. Your needle will have to be a line object in Access. Access is unable to rotate objects by degrees, but you can calculate the start and end points of a line to simulate a needle. That is the only way you can do it.

There are formulas for calculating those points--I will have to find my sample and then I can share.
Jan 7 '15 #4

P: 66
Thanks, I am waiting for your sample file.
Jan 7 '15 #5

twinnyfo
Expert Mod 2.5K+
P: 3,482
This is actually quite involved.

Some principles to start with are that you need to determine the radius of your needle. This will be based on about half the width of your speedometer.

Then, based on the image you are using, you need to find the center point of the needle. Based on the radius of the needle, and the percentage complete from 0 to 100% (assuming 180 degrees of motion, would translate to 0 - 180 degrees). Then, translate those degrees into radians (because that is how Access calculates), use the sine and cosine to determine the location of the end point. Based on how far along the speedometer is, you change the slant of the line and resize the object.

I remember when I first tackles this fun little project that it took me several hours to get all aspects of the clock to work correctly, as the center point of the line must remain constant, but the sizing handles and slant moves based on the location of the needle along the clock face.

Because of the complexity of this I may opt to write an article in this forum and post a link to that article on this thread. Right now, I just don't have the time to give all the details on how to get where you want to be--and I don't want to just leave you hanging.

Give mea few days to try to put something together that would be meaningful and less rushed.

Thanks!
Jan 7 '15 #6

P: 66
Cool, I can understand, but not everything when you go with this cos and sin formula as I never used those. I will be eagerly waiting for your thread. twinnyfo
Jan 7 '15 #7

P: 66
Hi twinnyfo, Do you have any update on the above?
Jan 12 '15 #8

twinnyfo
Expert Mod 2.5K+
P: 3,482
Yes!

Take a look at the attachment. This is a sample Speedometer gauge. The Heavy red line is a Line Object. Based on the Code, you must name this object "lnNeedle".

To use this speedometer, the following Code applies:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Const PI As Double = 3.14159265358979
  5. Const dblRadius As Double = 1.75 * 1440
  6. Const dblCtrH As Double = 2 * 1440
  7. Const dblCtrV As Double = 2 * 1440
  8.  
  9. Private dblRatio As Double
  10. Private dblRadians As Double
  11. Private dblTop As Double
  12. Private dblLeft As Double
  13. Private dblHeight As Double
  14. Private dblWidth As Double
  15.  
  16. Private Function UpdateSpeedometer(Current As Integer, Max As Integer)
  17. On Error GoTo EH
  18.     If Current > Max Then Exit Function
  19.     dblRatio = Current / Max
  20.     dblRadians = dblRatio * 180 * PI / 180
  21.     dblTop = dblCtrV - (Sin(dblRadians) * dblRadius)
  22.     dblHeight = dblCtrV - dblTop
  23.     If dblRatio < 0.5 Then
  24.         Me.lnNeedle.LineSlant = False
  25.         dblLeft = dblCtrH - (Cos(dblRadians) * dblRadius)
  26.         dblWidth = dblCtrH - dblLeft
  27.     Else
  28.         Me.lnNeedle.LineSlant = True
  29.         dblLeft = dblCtrH
  30.         dblWidth = -Cos(dblRadians) * dblRadius
  31.     End If
  32.     Me.lnNeedle.Top = dblTop
  33.     Me.lnNeedle.Left = dblLeft
  34.     Me.lnNeedle.Height = dblHeight
  35.     Me.lnNeedle.Width = dblWidth
  36.     Exit Function
  37. EH:
  38.     MsgBox "There was an error updating the Speedometer!  " & _
  39.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  40.     Exit Function
  41. End Function
Notes:

There are several constants:

PI: This is self-explanatory. Since MS Access does not have a constant for PI, we have to establish one.

dblRadius: This is the radius of the Line Control (the "needle"). Note that the radius of the Gauge is 2 inches (two inches high, four inches wide, but the Radius is two inches). For this example, we have set the radius to 1.75 inches (so it is not quite to the edge of the gauge).

dblCtrH and dblCtrV: These are the horizontal and Vertical center points of your Speedometer gauge. Remember, our gauge is two inches high and four inches wide. Because it is set all the way top/left, the center of the gauge (technically speaking, it is the vertex of the needle). Keep in mind that if you move the image to the right one inch, dblCtrH becomes three, and so on. This is important in terms of how the speedometer calculates points.

NB: Each of these three variables are multiplied by 1440. This is because MS Access uses "twips" when placing objects on the screen. A twip is 1/1440th of an inch. Once inch = 1440 twips.

The next variables are used within the calculations and are reasonably self-explanatory, as there will be a ratio of "completeness" that you want your speedometer to show, which will be converted into radians (not degrees, as MS Access uses radians for it's geometric functions).

We also need to know the Top, Left, Height and Width values for your line (the Needle), which will be calculated based upon the values you send to this function.

Of note, the values received by this function are integers, so if you need larger values, you can go to a Long Integer instead.

I did not add error trapping for this, but the assumption is that you will always be sending positive numbers to this function. you can easily add that error trapping into this module, or verify before you send values to the function.

This function does check whether the current is greater than the Max (we assume from 0 to 100%).

First, we calculate the ration of completeness. Then, calculate the radians. Certain mathematicians will say that
Expand|Select|Wrap|Line Numbers
  1. dblRadians = dblRatio * 180 * PI / 180
should really be
Expand|Select|Wrap|Line Numbers
  1. dblRadians = dblRatio * PI
which it could. However, we are only assuming a maximum travel of 180 degrees (the first 180). With a little extra programming you could have 360 dgrees of needle travel or 45--whatever you want. However, most of us think of a Speedometer as a half-circle (more or less). The second 180 is actually part of a formula to calculate Radians (Degrees * PI / 180). Your choice to keep it as is or change it.

The dblTop and dblHeight are always based on the Sine of the Radians, multiplied by the dblRadius of the Needle. This sizes the needle appropriately.

The If...Then checks to see if the needle is more than half way around the dial. If not, the line slants one way; if so, it slants the other. Then, we calculate the other values for the size and shape of the needle.

Finally, we assign our calculated values to the needle Line Object.

To use the Speedometer in your form, just call it as such:
Expand|Select|Wrap|Line Numbers
  1. UpdateSpeedometer 10, 100
will indicate 10% complete.

Hope this hepps!
Attached Files
File Type: docx Speedometer.docx (37.0 KB, 722 views)
Jan 12 '15 #9

P: 66
Wow!!! First of all thank you for consolidating this detail in a very detailed manner. I will take a look at it and get back to you if I have any query.
Jan 13 '15 #10

P: 3
Hi twinnyfo

Would you be able to upload the sample database? I could just find a word document.

Thanks in advance
Jan 19 '15 #11

twinnyfo
Expert Mod 2.5K+
P: 3,482
@jkdbd,

The Word document merely shows an example of a speedometer gauge. The rest of Post #9 has everything you need to create a speedometer in your DB.
Jan 21 '15 #12

P: 3
Thanks twinnyfo.. I will refer to the post #9
Would you be able to send the frmSpeedometer form as per the word document?
Jan 22 '15 #13

twinnyfo
Expert Mod 2.5K+
P: 3,482
Here it is. Thanks!
Attached Images
File Type: jpg Speedometer.jpg (19.9 KB, 2054 views)
Jan 22 '15 #14

P: 3
Thanks twinnyfo.. Much appreciated.
Jan 22 '15 #15

Post your reply

Sign in to post your reply or Sign up for a free account.