AnsweredAssumed Answered

strange calculation problem

Question asked by denno on Dec 3, 2010
Latest reply on Dec 3, 2010 by denno


strange calculation problem


I have a database that contains 3 tables:

PatientTable--ER patient visits; currently contains data for 7/10 & 8/10

DoctorTable--doctor names

HoursTable--hours worked each month by each doctor

I have aggregate fields in DoctorTable that calculate number of patients seen, number of hours worked & pts seen/hour. One of the relationships between the tables uses start date and end date in DoctorTable related to date in PatientTable and HoursTable to limit to a selected month.

My problem shows up with the patients/hour calculation.

1. When I limit DoctorTable to 7/10 by using appropriate dates in start/end, all of the calc fields return correct data

2. When I limit DoctorTable to 8/10 by using appropriate dates in start/end, all of the calc fields also return correct data

3. When I include both 7/10 & 8/10 in DoctorTable, the number of patients seen and number of hours worked results are correct but the patients/hour result is wrong.

For example, here are the calc results I get for 1 of the doctors:


# seen 242

hrs worked 116

pts/hour 2.1


# seen 244

hrs worked 109.5

pts/hour 2.2


# seen 486

hrs worked 225.5

pts/hour 4.2

Pts/hour calcs correctly when I limit by individual months but not when I include all data.