5 Replies Latest reply on Dec 3, 2010 1:22 PM by denno

    strange calculation problem



      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.