# high performance query/function needed

Hi experts,

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.

With appreciation,
Ben
LVL 4
###### Who is Participating?

x

PresidentCommented:
All right, so then:

<<1-check if there are no more schedules days per week then allowed (two schedules for same day are considered as one).>>

This is simply the number of allotments vs the number of days scheduled.

<<2- check if there are no more hours for this date then allowed (will have to combine all scheduling for this date).>>

1.  Sum the schedule for each day.
2.  Get the allotments and sort from low to high.
3.  For each day, consume one allotment.  At the end, you will have either have left over allotments or be fully scheduled (don't believe we can be over scheduled, because were going to stop a schedule rec from being created if that happens, but are you going to allow allotments to change?  If so, future schedules may no longer work).

Both tasks can be accomplished in the same pass through the data.

I think chaau's suggestion:

"I think you need to load all available allowances for the patient upfront, on Form Load."

Was a good one, assuming that the allotments can't be changed in the middle of scheduling.  I'm also wondering if it's important for you to know if someone did or did not use their full allotments for past schedules.  If so, then were going to need to save the allotments along with the schedule for historical purposes.

I think the other thing that needs to be done in the scheduling process is to show the allotment used against any given scheduled day.   I think it's going to be very confusing to a user if that's not done.

For example, I do the entire week, and then go back and change the number of hours on a wed, which then forces a different allotment to be used for that day.  Now the remaining days of the week may no longer work.

So now that we understand this, how do you envision all this working?  Is this something the computer will do on it's own, or is someone going to sit down and do this one patient at a time?

In either case, you'll need a form to view/change a schedule, so let's start there.

Because of the allotment process, I can't see working with anything other then a week at a time easily.

So maybe a form with a "start of week date", the scheduled records for that week, and a summary of total hours for each day and the allotment assigned for that day.

Or maybe two subforms, one showing the schedule recs, and the other showing the allotments and the day they are assigned.

Do you have something already in the works?   If so, a screen shot would be great.

The logic I outlined above can be implemented in a number of different ways, and what you do in the form or want as a process will determine how we actually go about it.

Jim.
0

Commented:
I think you need to load all available allowances for the patient upfront, on Form Load. Also, load all "used" hours and days. Display this information on the form for data entry people to see. When they update the record it will be the matter of testing the already loaded values in the VBA code and issue a proper warning message to the user.

What do you think about that?
0

Author Commented:

Currently its a Continuous Form that's being used for dual purpose, data entry and reporting,
users just filter on top and when click on filter button, then they see the desired data, for example today's records and they do all edits there as well.

just assuming if i display those calculated values in the form as you're suggesting, it will cause slowness every time they filter or even scroll up/down the records...
0

PresidentCommented:
<<therefore in the PatientsAllowances Table, I created 6 fields as below:
DaysPerWeek1, HoursPerDay1, DaysPerWeek2, HoursPerDay2, DaysPerWeek3, HoursPerDay3, >>

This is a bad design and will make this problem more difficult.  You should never have repeating fields in a table.   What you should have is:

DaysPerWeek
HoursPerDay

and three records in the table for each patient.

<<1-check if there are no more schedules days per week then allowed (two schedules for same day are considered as one).>>

With the proper design, it's a simple SQL operation to count records.

<<2- check if there are no more hours for this date then allowed (will have to combine all scheduling for this date).>>

Same deal.  Change your table design.

Jim.
0

Author Commented:
hi Jim,

there are two reasons i have them in this format.

1- at the the time we started it was only one set of them, later another one and only lately that the third option was added, and since there is a significant logic build into the system in this direction, i didn't want to re-write everything, at least for the time being...

2- don't think that this rule is always true, that whenever you have couple of fields of the same data type, there should be split into a one to many relationship design, consider for example an employees table that have 3 date fields DOB, DateHired, DatePromoted etc...,
of course it all depends what are you planning to do with those fields, and how they relate to each other...
however in this case, i am under the impression that there must be 3 types of calculation, for each set separately, therefore (in addition to the above..) is why we have it this way.

3- do you have the calculation that would work efficient for this situation?
In case the only way is to design it the way you're proposing, I am ready..
0

PresidentCommented:
<<1- at the the time we started it was only one set of them>>

That's the usual deal<g>

<< don't think that this rule is always true, that whenever you have couple of fields of the same data type, >>

Ah, but your tripped up there.  Relational modeling is not about data types, it's facts about some "thing" and that's what the rule is about.

The example you give are all facts about an employee.  The meaning of the value in those fields are all distinct from each other.  It's irrelevant that they all happen to be dates.

That's not true for what your doing now.   One way to illustrate that is just by having someone who has only one allowance of days and hours.

For any given table, I should always be able to fill in all fields, not be forced into entering a null.   In this case, for DaysPerWeek2, HoursPerDay2, DaysPerWeek3, HoursPerDay3, I'd be forced to do that.

Another way to say that; can I shift the values of DaysPerWeek1 and HoursPerDay1 into the 2's or 3's without loss of meaning?  Yes I can.   Try that with the three dates of your employee records.

That points to the fact that in reality, you have three instances of the same thing in a single row.

<<3- do you have the calculation that would work efficient for this situation?
In case the only way is to design it the way you're proposing, I am ready.. >>

With the change in design, SQL will now work efficiently for you.

<<check if there are no more schedules days per week then allowed (two schedules for same day are considered as one).>>

Group schedule by date for a week, and Sum number of days   vs number of allowances

<<check if there are no more hours for this date then allowed (will have to combine all scheduling for this date).>>

Not sure I get this one, how can there be no more hours for a specific date?   Are you just saying the total of all hours for a given week exceeds the total of all the allowance hours?

Jim.
0

PresidentCommented:
One other thing to keep in mind; SQL was designed to work with normalized databases.   Because of that, it wants to work with sets of records, not work sets of fields.

For example, right now if I asked you to total all the allowance hours, you'd have to look at each of the three fields in the records.

With the normalized design, you only need to do SUM([HoursPerDay])

and if someone says at some point "we now need four", your changing your design.  Properly normalized, you'd just need to add records.

Having to change your design because you need to record more of something you already have rather then simply adding another record is a tip-off that your design is not correct.

Jim.
0

Author Commented:
hi Jim,

I see you point, but still have some questions.

In the case of Employees example above, with some fields having the same data type, you sort of agreed that if each field have a different meaning, they can just fit in one table, even tough you are storing null in some fields and also would have to modify the design of the table for each additional field in demand..

Therefore i guess your main point is that they serve here the same purpose.

Now let me clarify the request a little bit more

if someone has DaysPerWeek1 = 3, HoursPerDay1 = 4, DaysPerWeek2 = 2, HoursPerDay2 = 3.

the function must check for the following:

1- if total days scheduled this week will not exceed 5
2- total of hours this week will not exceed 18 (3x4)+(2x3)
3- if 3 days were already scheduled for 4 hours, then any additional schedule can't be more then 3 hours.

Giving these details, you're right that for #1 and #2 normalized tables are appropriate, however since I was most concerned on how to do the last request #3, didn't think it would make a difference for that, especially that my users came up now with a third set of allowances, just to complicate the situation a little more...:)

Ben
0

PresidentCommented:
Ben,

<<In the case of Employees example above, with some fields having the same data type, you sort of agreed that if each field have a different meaning, they can just fit in one table, even tough you are storing null in some fields and also would have to modify the design of the table for each additional field in demand..>>

No, it's not the same thing.   In regards to storing a NULL, is it because I don't know the answer or is it because there can never be an answer

I the case of DOB for example, I may have neglected to ask an employee for it, but every employee does have a DOB.  That is a fact about an "employee" and every record can be filled in.

In contrast, someone may only have one allowance and will never have more then one.  So I can never fill in values for the 2 and 3 fields.   So records in each row have a different "shape" (number of columns), which indicates that I'm keeping track of multiple allowances inside a single row or that columns belong to different "things" and should be in another table.

<<3- if 3 days were already scheduled for 4 hours, then any additional schedule can't be more then 3 hours.>>

It's not clear to me how you get there from:

if someone has DaysPerWeek1 = 3, HoursPerDay1 = 4, DaysPerWeek2 = 2, HoursPerDay2 = 3.

Your allowance says that they have (3 x 4) and (2 x6) = 24 hours.  Even pulling in rule #2 (18 hours max), then they still should have 6 hours left.

Suffice to say though that with a re-design of the table, the SQL will become much more efficient.   I'm nto saying you can't work with it the way it is now, but it will be more difficult to accomplish any task with the allowances table if it's not changed.

Jim.
0

Author Commented:
hi Jim,

Sorry for not being clear enough at explaining the request here...

this is a situation where rules are very strict here, and anything violating them will end up in loss of money to the client.

the rules are as follows
1- every patient has a total qty of days per week they are covered for sessions.
2- there is also total qty of hours per week allowed that cant be exceeded.

now all the above we already discussed and rules are in place,

what is missing is the following rule, that in my eyes are the most difficult to apply ( as you see i'm having even a hard time explaining it:) ).

3- there cant be more hours per day than allowed, even if someone still have hours unused for that week.
therefore in the example above (3 days x 4 hours, 2 days x 3 hours), they cant be scheduled for 18 hours all in one day, the same is when they had already been scheduled 3 days for 4 hours, although there is still 6 hours left for this week, they must be divided into two days sessions, since the remainder allowance is for 3 hours per day.

Please let me know if this is clear now,

Thanks
Ben

p.s about the data modeling issue, from your latest post can i take that if an employee can have from 0 to 4 phone numbers, they do belong in a separate table as opposed of creating fields in the employee table like HomePhone, WorkPhone, Cell etc?
0

PresidentCommented:
On the allowances, a question:  Must I always schedule based on one of my allowances?   In this case, I can only schedule 4 or 3 hours (assuming I have no schedule already)?

Can I be scheduled for five hours on a day?

<<about the data modeling issue, from your latest post can i take that if an employee can have from 0 to 4 phone numbers, they do belong in a separate table as opposed of creating fields in the employee table like HomePhone, WorkPhone, Cell etc? >>

Yes, you would.  Let's say we have

HomePhone
WorkPhone
CellPhone

all are really a "method of contact", not an "employee".   So they should be split out.    Also, what if the employee has a second office number?  or an emergency contact number?  Your back again to changing the design rather then simply adding a record.

There's also the test of "can I fill this field in for every record?"   The answer is no, because not all employees may have a cell phone.

However some will break the rules at this point and keep all these inside the employee record.  Part of the reason is that in real life, an employee only has so many numbers and so many different types of numbers.   You can ask for the basic types and ignore the fact that an employee may have two office numbers for the most part and that will not be difficult.

Also what's confusing here is that besides the number itself, there also is the type of number implied.  Many believe that because they are different types, there's no reason to break them out.  ie. I can't swap work and home phone numbers and retain the meaning of the data those fields represent.  So they are different facts about an employee.

In the case of the employee without a cell phone, they might enter a value "NA", for not applicable to indicate there is none, but that's not a phone number.   But you can live with a system like this and for most applications, that data model will work pretty well.

So this is one place where you'd ask:

"Should I normalize further?   Yes."
"Do I really need to normalize further?"

and the answer might be no.   Of course you'd have to live with the consequences (what if someone has two office #'s), but for the most part, you can live with doing something like this.

Certainly things do become a little more difficult.  For example, if someone said "how many phone numbers are on file", you have to dive into each employee record, look at the individual fields, and figure out if they had "NA" or an actual phone number.

Designed properly, all you would need to do is count records:  Select Count(*) from tblEmployeeContactInfo

Jim.
0

Author Commented:
hi Jim,

On the allowances,

1- yes, i can only schedule according to allowances

2- correct, no date can have more hours scheduled then allowed (in this case some 4 and some only 3 hours), however a patient can have more then one schedule par day as long as it doesn't exceed the total allowance per day when computing them all.

3- no, in this example no day can have 5 hours of schedule, for example 1PM-3PM + 4PM-6PM is fine, for 3 days a week only, the forth and fifth day of schedule can only have 3 hours, no matter if they are at once like 1PM-4PM or divided like 1PM-2PM + 3PM-4PM + 5PM-6PM...

here are my concerns if splitting them like you're suggesting

assuming the EmpPhones table will have 4 columns
ID, EmployeeID, PhoneType, PhoneNumber

1- very often I get a request to create a report that has employee name and next to it all 4 phone numbers, employees contact people and addresses.
now they want all those info should appear in one row, so the report would look like an excel sheet of rows and columns.
if i had to split those info into 4 tables (Employees, EmpPhoneNumbers, EmpContacts, EmpAddresses), how would i be able to accomplish that?

Another concern is that I will end up having dozens of tables for data all belonging to an employee itself, when I take in consideration the many aspects this app have to handle, I could end up easily with hundreds of tables, what would really cause an enormous overhead on my shoulders..

just to mention one additional issue, we have currently an access file used for reporting that is linked to our SQL back end, the users who are familiar on designing simple queries use them in a daily bases,
now if i have to re-design it to full normalization as above, how would they be able to deal with that...

One additional thing, isn't this a more efficient way of typing info when the label next to it already explains what info belongs here, (Home Phone: ___-____) as opposed of having to select the type of info you're entering?
0

PresidentCommented:
<<2- correct, no date can have more hours scheduled then allowed (in this case some 4 and some only 3 hours), however a patient can have more then one schedule par day as long as it doesn't exceed the total allowance per day when computing them all.>>

Still not getting this.  You talk about total allowance per day, but none of the allowances specify a day.  Back to this example:

<<if someone has DaysPerWeek1 = 3, HoursPerDay1 = 4, DaysPerWeek2 = 2, HoursPerDay2 = 3.>>

I have (3 x 4) + (2 x 3) or 18 hours for the week.   What stops me from taking all 18 hours on the first day?

Jim.
0

PresidentCommented:
<<Another concern is that I will end up having dozens of tables for data all belonging to an employee itself, when I take in consideration the many aspects this app have to handle, I could end up easily with hundreds of tables, what would really cause an enormous overhead on my shoulders..>>

And that's why many take shortcuts, like keeping all the phone numbers in an employee table.  The gain is not worth the effort.

Jim.
0

Author Commented:
HI,

the fact that there is HoursPerDay1 = 4 already says that for each of those 3 days in DaysPerWeek1, do not schedule more then 4 hours.

The HoursPerDay1 relates to the DaysPerWeek1 only.

The same is with the two other sets HoursPerDay2 & HoursPerDay3, all relate to their corresponding field (HoursPerDay2 for DaysPerWeek2 and HoursPerDay3 dor DaysPerWeek3).

However the only thing they share is that all are allowances per week, meaning they can all be scheduled for the same week.

therefore we end up in this case with allowances of 5 days of schedule per week and 18 hours per week in total.
0

PresidentCommented:
OK, that's where I thought you were heading.

3 x 4
4 x 2
1 x 5

give me two possible schedules just to confirm (I had typed up a couple, but I'm still not sure I have it).

Jim.
0

PresidentCommented:
In fact first, could I have those allotments?   There's more then 5 days there (I'm assuming scheduling is Mon-Fri).

A couple of examples would be good.

Jim.
0

Author Commented:
1- schedules are made 7 days a week, week goes from Sun to Sat.

2-your example isn't possible as it would mean more then 7 days per week

let's modify for the below:
3 x 4 (meaning 3 days up to 4 hours per day)
2 x 3 (meaning 2 days up to 3 hours per day)
1 x 5 (meaning 1 day up to 5 hours)

this week in Feb goes from 16-22
Sun  Feb16   1PM-5PM
Mon Feb17   1PM-5PM
Tue  Feb18   1PM-5PM
Wed Feb19   1PM-4PM
Thu  Feb20   1PM-4PM
Fri    Fen21   1PM-6PM

this example would be the exact meaning of this allowance

if I would put in Wed 1PM-5PM would violate the rule as 3 days of schedule with 4 hours are used already.

if I would add schedule for Sat Feb22 would violate the rule as no additional days of schedule were left.

If I would split Fri schedule in two, 1PM-3:30, 5PM-7:30 is fine as they sum up to 5 hours and the last line of allowance states, 1x5

I can also choose Sat instead of Sun or any given day.
0

PresidentCommented:
So it's one allowance per day not matter what?

and will a schedule for a day always reflect the full allowance? In other words, if it comes down to Sunday, and I have 1x3 allowance left, can I schedule two hours, or must it be three?

or better yet, with your example:

3 x 4 (meaning 3 days up to 4 hours per day)
2 x 3 (meaning 2 days up to 3 hours per day)
1 x 5 (meaning 1 day up to 5 hours)

can I schedule only two hours on any given day?

Last, what's the minimum schedule block, one hour?

Jim.
0

Author Commented:
1-correct, no day can consist of two allowances.

2- you can always schedule less then allowed.

3-there is no minimum, although don't recall seeing schedules for less then half hour.

4- there is one exception were its a live in case, meaning they are scheduled for 24 hours, and the way this works is they put in 8:AM-8AM, If this causes problems, lets put in aside.
0

PresidentCommented:
Ok, so on point #2,  then we consume the lowest available allowance?

Jim
0

Author Commented:
you got it!
0

Author Commented:
Hi Jim,

I see you're really putting efforts here to get it done, this gives me some confidence that we will finally reach the goal...

As you requested, I am attaching both, a screenshot of the form used for adding/editing single records, as well as the form itself with the code behind it.

As described above, its a continues form that the users filter on top for what they want to see on screen and then they go edit each individual record as needed.

I am also including a screenshot of the form they enter authorization info.

With great appreciation
Ben
Doc13.doc
test-schedule.zip
0

PresidentCommented:
Yikes that's a busy form.

So what your looking for is a go/no go for a schedule record whether it's being added new or updated?  Nothing about telling them why it won't work?

I don't see anything on the form that tells me what the allotments are or what is available.

Jim.
0

Author Commented:
Hi,

1- correct, I only need to prevent them from entering unauthorized schedules, a message would come up saying this is not authorized, and they have on top of the screen a button with a caption master schedule that brings up the authorization screen, in case they need to look up.

2- on the module of the form attached in the before update event you will find the following:
``````            If MoreThenAuthorized Then
MsgBox "Cant schedule more hours then authorized", vbCritical
Cancel = True
Exit Sub
End If
``````

this actually calls the MoreThenAuthorized function that contains the current logic to prevent schedules not unauthorized due to more days or hours in total, as described above, this two are already implemented in the system.

the following lines in the function actually checks for that in particular,

``````    If Nz(Me.ComboPatientsBillingCodesID.Column(2), 0) > 0 Or Nz(Me.ComboPatientsBillingCodesID.Column(4), 0) > 0 Then
iHours = (IIf(Me.ComboPatientsBillingCodesID.Column(1) = "", 0, Me.ComboPatientsBillingCodesID.Column(1)) * IIf(Me.ComboPatientsBillingCodesID.Column(2) = "", 0, Me.ComboPatientsBillingCodesID.Column(2))) + (IIf(Me.ComboPatientsBillingCodesID.Column(3) = "", 0, Me.ComboPatientsBillingCodesID.Column(3)) * IIf(Me.ComboPatientsBillingCodesID.Column(4) = "", 0, Me.ComboPatientsBillingCodesID.Column(4)))
``````

that's said, the control on top of the screen named ComboPatientsBillingCodesID contains that auth info.

Thanks again,
Ben
0

PresidentCommented:
OK, so your a lot further then I thought and with that, what are you stuck on?

I gave you the logic here:

http://www.experts-exchange.com/Database/MS_Access/Q_28363513.html#a39870039

The only real wrinkle is that if we are editing an existing record, then we need to ignore the existing records scheduled hours and use the hours were changing to.

I'd probably do that simply enough by:

1. Calling MoreThenAuthorized() with the old and new hours for the current record being edited.  In the case of a new record being added, old hours would be zero.  You also need to be passing the schedule date.

2. In MoreThenAuthorized(), open a recordset that sums the date range required which encompasses the scheduled date passed and uses GetRows() to place it in an array.

3. Then reduce the scheduled day in the array by the old hours passed, and add in the new hours passed.

At this point, you'd then have an array of the allotments and a array of scheduled hours for the week.

You'd then walk through those using the logic I posted in the comment and will either make it to the end of the scheduled hours without running out of allotments or not, which is your yes/no if the new schedule would work or not.

Jim.
0

Author Commented:
Hi Jim,

I am still in middle of analyzing what entails here to get this to work..

1- wonder how long do you estimate this logic would take to run? (we are talking from a table that currently contains over a half million records and its growing pretty fast..),
taking in account that this will be running every time a user tries to save a record, and all in addition to what its already in place..

2- maybe I would first do the logic in access that I am more familiar with, and then see how to convert it to a t-sql function (since the data is store in SQL), and call it somehow from access..

3- I see you're mentioning how to deal with edits, I usually do one sql for all values where id <> me.id, and then add the values from current record, is that a workable solution?

Thanks
Ben
0

PresidentCommented:
<<1- wonder how long do you estimate this logic would take to run? >>

As long as there is an index on the patient ID and the date, it should be very fast, even without pushing it over to SQL.  A second or so at most.  There's not that much data to work with.  The recordsets would be very small.

<<and then see how to convert it to a t-sql function >>

The problem there is the data would first need to be saved for SQL to see it.  You were talking about stopping the user from entering a new record or changing an existing one before it took place.

<<3- I see you're mentioning how to deal with edits, I usually do one sql for all values where id <> me.id, and then add the values from current record, is that a workable solution?>>

Yes, that's exactly how you'd exclude the current record.

Jim.
0

Author Commented:
Hi Jim,

I guess this will be my next project to do that logic, & will keep you posted..

The problem there is the data would first need to be saved for SQL to see it

Not sure what you mean, since we are not going to include the current record as part of the recordset, as mentioned above (id<>me.id), the function would just take the current id as one parameter, and the new values being saved as additional parameters, then return true/false value as you mentioned?
0

PresidentCommented:
Yes your right...I wasn't thinking that through clearly.  It's no different whether you do it on the Access side or the SQL side.

In either case, the process would be the same.

Jim.
0

Author Commented:
Thanks allot Jim, I really enjoyed this thread,
Hope to get to work on this shortly,

(Just in case you had already written such a logic in t-sql, I would appreciate if you can post it here).

Thanks
Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.