Get the next avaliable record

Question

Hi, I have a single form that is bound to a table of customers we have to call them one by one. The environment is multi-user and the database have been split in front-end access forms and back-end SqlServer data. So the problem is more than obvious. How can avoid two or more users-agents to call on the same person at the same time. One thought is to assign equally parts of records in each user-agent. So if we have 10.000 records and 4 agents then we have to assign to each agent 2.500 records. But this has many disadvantages. A more sophisticated way would be to have one list for all agents and a next button would have to pick the next available record for call. But in what way can we guarantee the consistency and stability of this function. I thought to work with a flag field. So when an agent click on "Next" button then a field (witch is for this purpose only) is flagged for that record for as long as the agent use that record before continue with the "Next" available record! So if in the same time another user asks to pick up a next record for call then the system will avoid to bring the flagged records for call. Ok this is simple solution but I have 2 questions.

1. I have to make a form for administrator of the project (supervisor). This form will have all the records in continues form view or datasheet view. If a record is updated with a flag because is in use for call by an agent then will the flag appear immediately in the form of supervisor?

2. I think that every time an agent closes the front-end access application then flags as garbage will stay at the customer table. Maybe a garbage cleaner procedure to solve the problem is an option. When the front-end form closes then will run the procedure to clean the flag. But if the agent closes the front-end access before closing the form will the OnClose form event appear? Are there events related to database and not only to controls?

Answer 1

Don't use flags. They have normally no place in a relational model. Flags are normally a row in a table. When the row exists, then the flag is set. Otherwise not.

In your case a separate table makes sense, especially as it fulfills a role: It's queue table. So stuff all pending rows into the queue. Each agent takes a row from the queue when the call starts. This means that it is moved from the general queue table into an activity table. There stays the row as long as the agent is working on it.

The blue tables are your normal tables. The yellow one are the scheduling tables. The 1:1 relations in the scheduling tables ensures that only one agent can work on one item at a time. When a item is finished - here the agent must press a button to do this - the items are removed from the working list and from the queue.

Answer 2

Thanks Stefan... I was in your mind... I just had the same idea about records as flags in a separate table (WorkItems). But I think that the PendingItems is not really necessary in my situation because I need all the records from Items and if an agent move to another record does not mean that the previous record has to be removed from our plans because the results of the call could be a recall in the near future (automatic or scheduled).

Also if the agent that is in a call record closes the front-end without explicitly declare the end how can I make sure that I do not have related record in the WorkItems?

Thank you very much

Answer 3

That's the reason why we need pending items. It's the table holding the items which must be worked on.

When a recall is necessary, it is placed again in the pending table at the time where the call is necessary.

Cause it is Access, you cannot ensure it. The most common approach is a hidden form, which reacts on the close event. But this is not safe. E.g. a user just shutting down the machine will kill processes without that mechanism.

But the cool thing about the 1:1 relation: An agent cannot work the next day, as long as he don't clean-up the situation.

Also you can run a process in the night to inspect that table and send an report to the agent or supervisor. But imho the key is: An agent should gracefully leave the application and thus close the work items before doing so. Kinda code of conduct.