I have a lookup table called tbl_Stars it has all of the fields that define the possible stars measurements: measurecode, submeasurecode, starlevel, lowscore, highscore. I created the following VBA Function in a module.
Code:
Public Function Starslevels(ByRef msr As String, Level As Long) As Double
Dim strsql As String, rs As New ADODB.Recordset
strsql = "Select low From tbl_stars Where submeasure = """ & msr & """ and stars = " & Level
rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then Starslevels = rs!Low / 100 Else Starslevels = 0
rs.Close
Set rs = Nothing
End Function
In the query I have 2 columns that call that function with the 2 parameters. First one is whatever submeasurecode I'm processing and the second either a 4 or a 5 depending on which level it is. The Graph has 3 horizonal bars. The top bar is the PCP's actual score, second bar is the low for level 4 and the third bar is the low for level 5 so the PCP's can see with a quick glance where they are in respect to those 2 levels for each measure.