Auto assign tasks to employee based on conditions (1 Viewer)

[kokowawa]

@arnelgp
@isladogs
As I mentioned that I am working on travel agent request and I am facing a new challenge,

It is New Service Request Auto assign : what dose it mean ? when the company receive a new service request , this request should go to a specific employee with a specific capabilities of handling for each employee for example some employees can handle only flight requests ,some employees can handle only hotel and transfer requests some employees can handle all services , and since there many employees and many requests so the travel agent would like to auto assign the requests received fairly to all employees based on below conditions on his turn : (more like auto assign tasks to next available employee that have the right conditions )



-Employee should be an operation Department staff based on department from Employees Table

-Capabilities of employee from Employees table Ex: can handle flight request , can handle Hotel Request , can handle Transfer request.

-Type of request based on service Type from Requests table

-Max allowed open requests to the employee per day from Employees table the employee cant have new request if he have reached the limit , the request goes to the next employee with the right conditions

-Sum of requests that marked as “open” in requests Table. The employee can take the request if sum of opened requests he have is less than or equal to the max allowed open requests .

So when a new request received the system auto check the above and detect who have the right to handle this request based on the above conditions

For example the agent receive a request from the client and this request include hotels, flight, transfer . so the logic should check who have th turn and can handle all these requests and assign the request for him , what if there is only one can handle all and rest of employees can handle part of the service ? if this is the first request for the employee that handle all so he will take it , if the next request will have all services in it , the request should be separated to next employees that dose not have taken requests yet or didn’t reach the daily limit

And assign it to him by putting the employee name and employee code on the request .

What I am facing is how to put a logic via vba to this.



Attached the tables and the basic form that needs the logic

 

[The_Doc_Man]

I tried but I can't open your DB. It is a later version than my 2010 version so I can't touch it.

I might suggest that not all of this should be done in VBA. At least some of this should be managed by making your employee list based on a query that does some of that selection and computation for you. For instance, when considering employees who can handle the ticket, make a query that only selects folks from the right department in the first place.

Suggestion: When making the assignment, you don't need to put the employee name AND the employee code in the same record as long as the employee code is the Primary Key of that table and the employee name is available from the employee table. You can do a lookup or JOIN query later if you have this information. This works as long as there is no employee 0. (I.e. code is 0).

The problem I see for part of this selection / isolation process is that you have a many-to-many request. I.e. the task can have flight, hotel, etc. and you need someone who can handle flight, hotel, etc. - but the implication is that in some cases you have people who don't do hotels or who don't do flights. This makes the selection process extremely complex. The exact structure of both the task table and the employee table with respect to these abilities will be crucial and should therefore be carefully considered. Nicklaus Wirth, the "father" of the Pascal language, once said something that I will paraphrase (because my Polish isn't that good): 80% of all programming problems originate in bad data design.

A question you should ask is "What happens when, near the end of the day, the only agent who can handle all of the requirements of the next task has already reached the limit." Do you have to fake it by giving the task to someone who just comes close to the requirements? Break the limit? Delay the task until next shift?

I don't know how much direct help I can offer since I have an older version of Access and you appear to have been working with some of our really good members. Perhaps they will be able to give you more and better direction.

 

[kokowawa]

thank you @The_Doc_Man for your reply and taking an action to my post.

yes i did the DB in access 2016 however i have imported the tables and the forms to an older 2003 format . i hope it open with you. it is attached .

indeed i see it somehow complecated proccess atleast for my mind , thats why i asked for help from the proffisionals as you and the other respictive members .

i have thought in many things as you mentioned of making quires that filter most of the conditions and combine them in 1 query that give me the expected staff members but my mind will blow of thinking because of the complex , maybe some expert have a simpler idea to do it or do part of it .

is you mentioned in a question you should ask , yes there is many posibilites that may prevent this from happening which will lead us to add more conditions to control the proccess . however there is manul part might be used by the manager to monitor such cases and manual assign the request to the available employee even it is not his turn .

 

[Dreamweaver]

I've taken a look at your file in 2016 and found the following

I Would Remove the lookup fields below

1. You Use a lookup field in table for RequestedService I would make this another table
2. AssigneTo Is lookup Field
3. RequestMethod Is a listbox lookup
4. Destination Is a lookup
5. TransCarType
6. CRentCarType

You have hotel1, hotel2, hotel3 I would use a meny to many table for these

I think you are treating this like an excel file as you have to tabs Hotels, Flight that use the same table I.E. NewRequestDetails I would look at splitting these I do understand hotels and flight go hand in hand but Would still split them plus add tables for all the lists that that don't use tables.

Once you get that part sorted your'll find creating queries a simple job

 

[kokowawa]

I've taken a look at your file in 2016 and found the following

I Would Remove the lookup fields below

1. You Use a lookup field in table for RequestedService I would make this another table
2. AssigneTo Is lookup Field
3. RequestMethod Is a listbox lookup
4. Destination Is a lookup
5. TransCarType
6. CRentCarType

You have hotel1, hotel2, hotel3 I would use a meny to many table for these

I think you are treating this like an excel file as you have to tabs Hotels, Flight that use the same table I.E. NewRequestDetails I would look at splitting these I do understand hotels and flight go hand in hand but Would still split them plus add tables for all the lists that that don't use tables.

Once you get that part sorted your'll find creating queries a simple job

thank you for the reply , i have the same thoughts of spliting the tables , but i did them in 1 table coz i want to unifiy all coming requests in one list to be managed by the manager and also to get statistics of received requests like the screenshot below ,the request usually have minimum of details of the request and the agent speak to the client and after finishing the deal he creates compelet Booking in another form and other separate tables .
i will reconcider splitting them into tables if this help if there is a way to join multible tables in one list through a query or anything else like below pic.

 

[The_Doc_Man]

MickJav made some specific comments including the avoidance of table-level lookup fields. It is better handled by a JOIN type of query that can look up the value for you at QUERY level. Table-level lookups lead to massive confusion when trying to decide what is the actual value of the requested field. You can also defer the translation of any code to a form that can implement the combo-box style lookup on the form only. And there are form wizards to help with that kind of combo-box or list-box lookup.

I'm going to make a philosophical suggestion. You said "i did them in 1 table coz i want to unifiy all coming requests in one list" - to which I say, "No, wrong viewpoint."

One of the key elements to handling a diverse problem like this is "divide and conquer." (Old Julius Caesar had the right idea.) Use a "central" or "master" ticket that contains the client's name, date of request entry, and things that pertain to the overall problem. But have a bunch of child tables that have a parent/child relationship to the master ticket. (Parent/child is also called independent/dependent - both of which are frequently of the one-to-many style of relationship.) Since this is a travel issue, you might have to book multiple hotels, multiple flights or bus rides or train rides or cruises, you would have child tables for each of those. Then when you need to do something at the high level, you only touch the "master" ticket. But if you need to change flights, have a separate (possibly sub) form that is flight-oriented. I'm thinking "tabbed" form with a flight tab, hotel tab, whatever other services are involved. Just to clarify, flights are dependent on the master ticket because you wouldn't bother to book a flight if you didn't have a ticket requesting one. Same for hotels, other transportation, and other services.

Trying to jam everything together into a single record leads to massive issues in normalization. MickJav pointed to your multiple hotel fields. That same complaint might apply anywhere. You are still trying to design this, and NOW is the time to be sure you are aware of normalization. The whole point of normalization is that it helps you divide-and-conquer your monolithic, cumbersome problem. It lets you get away from a thing that looks like a spreadsheet with over 100 columns.

We have a saying, "can't see the forest because of the trees." But it is ALSO possible to turn that around, which is what that one-big-table approach will do. You ALSO do not want to be in a position that you can't see the individual trees because the forest is dauntingly large. You ALWAYS ALWAYS ALWAYS want to be able to break up the problem into smaller pieces that can be managed. Look at it in overview - but be able to work on its pieces in a manner that is convenient and efficient.

Think about it this way. Your people will be single-tasking this because they will book flights in a bunch, then call hotels in a bunch. If they have to jump all over the place, they will lose the continuity of their work. They will thank you for putting similar tasks together.

Therefore, may I suggest that you study normalization. You can search this forum for "normalization" but if you search the web, look for "database normalization" because there are other kinds of normalization. Also, if you do the web search, do your first reading based on .EDU sites. You can get good data from .COM sites, but they frequently will want to sell you something and are often pushing proprietary rather than general solutions. While it is possible to normalize a database out to 5 or more levels, most Access databases do fairly well if you can get them to at least 3rd-normal form.