Sum problem for query expression

[Stanggirlie]

We use an Access database to track our billable Medicaid units. They recently changed the rules that we are now tracking 15 minute units, but here is the kicker. If a contact is 1-15 minutes, that's 1 Billable Unit. So, if someone has a 17 minute contact, that's 2 billable units. If someone has a 31 minute contact, that is 3 billable units. You get the idea, right?

I have a table where our workers put in the begin/end times of their contacts and then I have a query that calculates the minutes of each contact and converts it to the billable unit(s). So each client can have multiple contacts per month.

Here's my problem. I want to take the units and add them all up by client so I just have a report that lists each client and the total number of units for that month. The problem is, every time I try to accomplish this in a query OR a report, the units are not adding up correctly.



For example, Client A had 2 contacts by our worker for September. One was for 17 minutes (2 billable contacts) and one was for 6 minutes (1 billable contact). So it should be simple to Sum my "Billable Contacts" field and get 3, right? Well, apparently it's not. When I Sum the field, it keeps giving me "2 billable contacts". Another example is Client B had 6 contacts by our worker for September. It should add up to 7 billable units. It's only adding up to 4.

I had to use a make-shift roundup formula to get the billable units right in my query. I used this since Access doesn't have a "roundup" function: IIf([billable activity]=Yes,Int([minutes]/15)+0.5,0)

Do you think this may be causing the problem? If so, how do I fix?

Is there anyway to push the minutes/billable units back to the table at all as a default value? I've tried putting in the expression: ((Hour([end time])-Hour([begin time]))*60)+(Minute([end time])-Minute([begin time])) in the default value of the table to calculate the minutes and it won't accept it saying it doesn't recognize the field names 'End Time' and 'Begin Time'. I know I can accomplish this in the form, but how do I push the data back to the table??

Anyone have any ideas? I really don't want to have to manually count thru each client's contacts to get the correct amount. Any suggestions???

[dcrake]

When you store the start and end times in your table I suggest you store them as minutes past midnight. 1 - 1400

Then query one add calculated fields

Client:[ClientName]
ActivityDate:[DateOfActivity]
Duration:Int([EndMins]-[StartMins])
Units:Int((Duration/15))

Then in query two bring in query one

Group by Client and Sum Units. This should give the totall number of units by each client. If you want to apply a date range then you can apply a filter to the Activity date using the Between Date lower And Date Upper parameters.

Remember: the names of fileds used above are for brevity and as such should be substituted for the actual names of the fields in your tables.

David

[Stanggirlie]

Minutes past midnight, huh? Interesting.

So...if the worker is entering in times like "9 am" and "10:43 am", can they still enter in the times like that and the table would conver it automatically? Or would they have to manually calculate the minutes past midnight and enter it in like "540" for 9am and "643" for 10:43am?

If so, I'm not sure if that will fly too well with them (they are very non-mathmatical people).

[dcrake]

You would let the users enter a time as normal and use functions to convert the times accordingly

Here are a few functions to play with

 
Function MinsToTime24(AnyMins As Integer) As String
Dim h As Integer
Dim M As Integer
Dim nTime As String
    h = Int(AnyMins / 60)
    M = AnyMins - (h * 60)
    nTime = Format(h, "00") & ":" & Format(M, "00")
MinsToTime24 = nTime
    
End Function

 
Function MinsToTime12(AnyMins As Integer) As String
Dim h As Integer
Dim M As Integer
Dim nTime As String
Dim AmPm As String
    h = Int(AnyMins / 60)
    M = AnyMins - (h * 60)
    If h > 12 Then
        AmPm = "pm"
        h = h - 12
    Else
        AmPm = "am"
    End If
    If AnyMins = 720 Then
        nTime = "12 noon"
    Else
        nTime = h & " " & Format(M, "00") & AmPm
    End If
    
MinsToTime12 = nTime
End Function

 
Function TimeToMins(AnyTime As String) As Integer
If AnyTime = "" Then Exit Function
    TimeToMins = (Left(AnyTime, 2) * 60) + Right(AnyTime, 2)
End Function

David