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

[johny6685]

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/index.php?showtopic=1979324

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

Thanks
Johny

[twinnyfo]

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.

[johny6685]

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

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.

[twinnyfo]

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.

[johny6685]

Thanks, I am waiting for your sample file.

[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!

[johny6685]

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

[johny6685]

Hi twinnyfo, Do you have any update on the above?

[twinnyfo]

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:

Option Compare Database
Option Explicit

Const PI As Double = 3.14159265358979
Const dblRadius As Double = 1.75 * 1440
Const dblCtrH As Double = 2 * 1440
Const dblCtrV As Double = 2 * 1440

Private dblRatio As Double
Private dblRadians As Double
Private dblTop As Double
Private dblLeft As Double
Private dblHeight As Double
Private dblWidth As Double

Private Function UpdateSpeedometer(Current As Integer, Max As Integer)
On Error GoTo EH
    If Current > Max Then Exit Function
    dblRatio = Current / Max
    dblRadians = dblRatio * 180 * PI / 180
    dblTop = dblCtrV - (Sin(dblRadians) * dblRadius)
    dblHeight = dblCtrV - dblTop
    If dblRatio < 0.5 Then
        Me.lnNeedle.LineSlant = False
        dblLeft = dblCtrH - (Cos(dblRadians) * dblRadius)
        dblWidth = dblCtrH - dblLeft
    Else
        Me.lnNeedle.LineSlant = True
        dblLeft = dblCtrH
        dblWidth = -Cos(dblRadians) * dblRadius
    End If
    Me.lnNeedle.Top = dblTop
    Me.lnNeedle.Left = dblLeft
    Me.lnNeedle.Height = dblHeight
    Me.lnNeedle.Width = dblWidth
    Exit Function
EH:
    MsgBox "There was an error updating the Speedometer!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Function
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 "completene ss" 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

dblRadians = dblRatio * 180 * PI / 180

should really be

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:

UpdateSpeedometer 10, 100

will indicate 10% complete.

Hope this hepps!

[johny6685]

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.

[jkdbd]

Hi twinnyfo

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

Thanks in advance

[twinnyfo]

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

[jkdbd]

Thanks twinnyfo.. I will refer to the post #9
Would you be able to send the frmSpeedometer form as per the word document?

[twinnyfo]

Here it is. Thanks!

[jkdbd]

Thanks twinnyfo.. Much appreciated.