strange calculation problem
I have a database that contains 3 tables:
PatientTable--ER patient visits; currently contains data for 7/10 & 8/10
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
# seen 244
hrs worked 109.5
# seen 486
hrs worked 225.5
Pts/hour calcs correctly when I limit by individual months but not when I include all data.