Counting multiples of hours

[Robb58]

I hope I've chosen the right place to ask this question or even if it's possible to do what I want...

I have a database of jobs which records the number of hours taken for each task. I have been asked if it's possible to add a function where, for accounting reasons, the total hours are also broken down into batches of 3 hours and shown as a points system in another field . For example:

up to 3 hours = 1 point
4 to 6 hours = 2 points
7 to 9 hours = 3 points
etc etc...

is this possible?

 

[Grumm]

Maybe you can use DateDiff to get the interval in hours :
http://www.techonthenet.com/access/functions/date/datediff.php

 

[Robb58]

Thank you Grumm, I shall give it a try

 

[arnelgp]

you can also use the Switch or IIF function, either in query or in subs:

in query:
Points: Switch([fieldNumberOfHours] > 6, 3, [fieldNumberOfHours] > 3, 2, [fieldNumberOfHours] > 0, 1)

or
Points: IIF([fieldNumberOfHours] > 6, 3, IIF([fieldNumberOfHours] > 3, 2, 1))

 

[Grumm]

Well i don't know if he stores the time (like begin and end of each task) or he already know how many hours each task needed to be completed.
If you don't know how many hours there can be, maybe a solution is to divide the number of hours by 3 and roundup all the numbers.

1/3 = 0.3333333 => roundup = 1
4/3 = 1.3333333 => roundup = 2
7/3 = 2.3333333 => roundup = 3
etc...

That way you don't need IIF or Switch. (Beter check of the math is correct but technically they can work for XX hours and you get the correct number of points.)

 

[Robb58]

I've taken your idea arnelgp and added it to my main form's VB code which seems to work really well but at the moment, If I want to extend the calculations up to more hours I have to add additional IIF statements as below:

me.Points= IIF([fieldNumberOfHours] > 15, 5, IIF([fieldNumberOfHours] > 12, 4, IIF([fieldNumberOfHours] > 9, 4, IIF([fieldNumberOfHours] > 6, 3, IIF([fieldNumberOfHours] > 3, 2, 1)))))

This works fine but looks a bit clunky. Is there a more elegant way to write it?

 

[cyanidem]

Maybe try Select Case?
me.Points= IIF([fieldNumberOfHours] > 15, 5, IIF([fieldNumberOfHours] > 12, 4, IIF([fieldNumberOfHours] > 9, 4, IIF([fieldNumberOfHours] > 6, 3, IIF([fieldNumberOfHours] > 3, 2, 1)))))

Select Case [fieldNumberOfHours]
    Case Is > 15
        Me.Points = 5
    Case Is > 12
        Me.Points = 4
    Case Is > 9
        Me.Points = 4
    Case Is > 6
        Me.Points = 3
    Case Is > 3
        Me.Points = 2
    Case Else
        Me.Points = 1
End Select

 

[Minty]

I still think the calculation suggestion is better - it will take any number of hours and return the correct value. If you create it as a function you can set a maximum return value if required.

 

[arnelgp]

create a table, ie tblPoints
fields:
Range1 (Double)
Range2 (Double)
Point (Integer)

sample:
range1:0 range2:3 point:1
range1:4 range2:6 point:2
range1:7 range2:9 point:3
... and so on


then just dlookup this table for the correct points:

=Nz( DlookUp("Point", "tblPoints", [FieldNumberOfHours] & " Between [Range1] And [Range2]"), 1)

note if dlookup doesn't find anything, the point will be 1. replace it with 0 if you want.

 

[Robb58]

Thank you all, I really appreciate everyone's contributions I've tried cyanidem's solution which works really well but I can't get arnelgp's table idea to work... I like the idea of being able to easily amend the points via the table, but I'm not sure I'm adding it the DlookUp code correctly as I keep getting a compile error in VB saying "Expected: Line number or label or statement or end of statement"

I've been thrown another bit of a curve ball with the points system... I've now been asked if its possible to adjust the range based on the "Job_Type" selected from a dropdown - e.g

1. "Job_a" 1-3hrs = 1point, 4-6hrs = 2points, 7-9hrs = 3points etc..
2. "Job_b" 1-5hrs = 1point, 6-10hrs = 2points, 11-15hrs = 3points etc..
3. "Job_c" 1-4hrs = 1point, 5-8hrs = 2points, 1-12hrs = 3points etc..

any ideas will be really appreciated

 

[Minty]

This is where a function would be the best way forward.
You can pass in your hours value and your job type and get the correct answer back.

You can also provide a default result if the job type isn't specified.

 

[Robb58]

Cheers Minty...
Any ideas how I go about writing that?

 

[arnelgp]

its better with table, ie: tblJobRange, fields:
job_type (text)
lo_range (integer)
hi_range(integer)
points(integer)

fill the table with pertinent data
job_type: job_a
lo_range: 1
hi_range: 3
points: 1

job_type: job_a
lo_range: 4
hi_range: 6
points: 2

... and so on

=dlookup("points", "tbljobRange", "[job_type] = " & chr(34) & [textbox1] & chr(34) & " and " & [textboxNumhrs] & " Between [lo_range] And [hi_range]")

 

[Minty]

I think a dlookup like that is going to get very slow if there are lots of results to calculate.

Why not use a function that is simple to maintain, you can even use a table to store the variances and use a query to populate responses within the function.

 

[cyanidem]

I agree with Minty, function will work better in this case.
Put this in standard module:

Public Function GivePoints(sJobType As String, nHours As Integer) As Long
Dim vPoints As Long
    Select Case sJobType
        Case "job_a"
            Select Case nHours
                Case 1 To 3
                    vPoints = 1
                Case 4 To 6
                    vPoints = 2
                Case 7 To 9
                    vPoints = 3
                Case Else
                    vPoints = 999
            End Select
        Case "job_b"
            Select Case nHours
                Case 1 To 5
                    vPoints = 1
                Case 6 To 10
                    vPoints = 2
                Case 11 To 15
                    vPoints = 3
                Case Else
                    vPoints = 999
            End Select
        Case Else
            Select Case nHours
                Case 1 To 2
                    vPoints = 1
                Case 3 To 4
                    vPoints = 2
                Case 5 To 5
                    vPoints = 3
                Case Else
                    vPoints = 999
            End Select
    End Select
    GivePoints = vPoints
End Function

And then you can just call it from your sub like:

me.Points=GivePoints(me.Job_Type, me.NumberOfHours)

or whatever suits your design.

 

[arnelgp]

if it is slow, i dont think it is, create a wrapper functions:

public function fnPoints( sJobType as Variant, dblHours as Variant) as integer
const JOB_RANGE_TABLE as string = "tblJobRange"
dim db as dao.database
dim rs as dao.recordset
dim strSQL as string

sJobType = Nz(sJobType, "")
dblHours = Nz(dblHours, 0)
strSQL = "SELECT Points FROM " & JOB_RANGE_TABLE & " WHERE [jobType] = " & CHR(34) & sJobType & CHR(34) & " AND " & dblHours & " BETWEEN [lo_range] AND [hi_range];"
set db=currentdb
set rs=db.openrecordset(strSQL, dbOpenSnapshot)
fnPoints = Nz(rs(0), 0)
rs.close: set rs=nothing
set db=nothing
end function

the thing here is your code should be generic, doesn't need to be change every time if there is a change in range or there is additional job type.
imagine if this is split database, you will change each fe, when you can only reflect the changes in the table.

 

[MarkK]

Simplest way IMO: Add a field called PointsFactor to the Jobs table. For job_a, PointsFactor = 3; job_b, PointsFactor = 5, etc ..., so you can vary the points factor by job. Then, in a query, calculate points with this formula . . .

Points: abs(int(-[Hours]/[PointsFactor]))

 

[Grumm]

I don't see why this needs an extra table and a 10 lines long code to do what a simple math solution can do. Divide the hours by the correct factor and you have the most generic formula that will work until your pc run out of memory to calculate the points.

Also what if you decide to change the factor ? you will have to create all those records again with new ranges...
Don't get me wrong, it is a good solution, but probably overkill for this specific job.

 

[arnelgp]

get your point, but where to we get the factor? hard coded. what if it change on time.
access and excel also has also many functions, overkill, just to make a simple spreadsheet.

 

[Grumm]

I would have a setting table (I store most of the static variables in there and load them when the application starts) where we can store that factor and make a get and set function. Then the admin has a special form where he can change the settings.
(default network directory's, default language stuff like that)