high performance query/function needed
Posted on 2014-02-12
I have a large table of patients schedules that among other fields are (PatientID, Day, From, To), where the day has the date of schedule and the from/to fields store the time of schedule.
Now each patient has a quantity of days per week field, and an amount of hours per day field, so let say if PatientID 1234 has DaysPerWeek = 3 and HoursPerDay = 5, I need the function to do the following before each schedule record get saved:
1-check if there are no more schedules days per week then allowed (two schedules for same day are considered as one).
2- check if there are no more hours for this date then allowed (will have to combine all scheduling for this date).
now in addition to the above..
there can be up to 3 sets of this quantities of allowances per patient,
therefore in the PatientsAllowances Table, I created 6 fields as below:
DaysPerWeek1, HoursPerDay1, DaysPerWeek2, HoursPerDay2, DaysPerWeek3, HoursPerDay3,
1- I guess once we figure out how to deal with one, the rest will follow..
2- I know this can be done with dcount and dsum functions, or maybe with some recordsed loop, however this is why i titled this post this way, because this is a large table and having to do all this calculations with those functions on the before update event would slow down the data entry process tremendously.
this is why i am counting on your experts to come up with something more efficient...
FYI- its MS Access front end mdb file application linked to SQL Server 2005 Database.