How To Allocate Tasks to Online Users In MS Access VBA 2007

[arjun5381]

Dear All,

I need your help to solve my problem, I have 2 Tables : (1^st ) tblTaskDetails (2^nd ) tblOnlineUsers as below sample data

I want to allocate all pending Accounts Numbers to All Online Users, Suppose 10 Accounts are pending for user allocation and currently 3 users are online in the system, then the system will automatically allocate these 3 online users to pending Accounts equally but most important there sequence like users A,B,C,A,B,C,A,B,C etc as like below Required Result.

1^st Table : tblTaskDetails (ORDER BY INCIDENT_DATE ASC)

Account_Number	INCIDENT_DATE
201200178	04-Apr-16 07:34:09 AM
921858299	04-Apr-16 08:25:43 AM
990990101	05-Apr-16 09:28:03 AM
990990151	05-Apr-16 09:33:03 AM
990246967	05-Apr-16 09:45:08 AM
990226021	05-Apr-16 10:04:10 AM

2^nd Table : tblOnlineUsers (ORDER BY Online_Users_Name ASC)

Online_Users_Name

A

B

C

D

E Required Result Account Number INCIDENT DATE If 1 User Online If 2 Users Online If 3 Users Online If 4 Users Online 201200178 04-Apr-16 07:34:09 AM A A A A 921858299 04-Apr-16 08:25:43 AM A B B B 990990101 04-Apr-16 09:28:03 AM A A C C 990990151 05-Apr-16 09:33:03 AM A B A D 990246967 05-Apr-16 09:45:08 AM A A B A 990226021 05-Apr-16 10:04:10 AM A B C B 948104306 05-Apr-16 10:29:18 AM A A A C 920617566 05-Apr-16 10:31:52 AM A B B D 920621842 05-Apr-16 10:33:14 AM A A C A 920844782 05-Apr-16 10:47:25 AM A B A B 936251646 05-Apr-16 11:01:30 AM A A B C 943999521 05-Apr-16 11:03:59 AM A B C D 991007397 05-Apr-16 11:06:34 AM A A A A 921511542 06-Apr-16 11:23:45 AM A B B B 944568550 06-Apr-16 11:24:10 AM A A C C 921511542 06-Apr-16 11:24:14 AM A B A D 909774256 06-Apr-16 11:31:02 AM A A B A 921773014 06-Apr-16 11:32:31 AM A B C B 988250007 07-Apr-16 11:34:58 AM A A A C 921779722 08-Apr-16 11:44:23 AM A B B D 991025088 08-Apr-16 11:58:41 AM A A C A 921841541 08-Apr-16 11:58:44 AM A B A B

[CJ_London]

I would expect something like this

SELECT T.*, Online_Users_Name
FROM tblTaskDetails T, tblOnlineUsers O
WHERE Unallocated=True AND Online=True AND (SELECT Count(*) FROM tblOnlineUsers WHERE  Online=True AND Online_Users_Name<=O.Online_Users_Name)=(SELECT Count(*) FROM tblTaskDetails WHERE Unallocated=True AND IncidentDate<=T.IncidentDate) mod A(SELECT Count(*) FROM tblOnlineUsers WHERE  Online=True)
ORDER BY IncidentDate

[arjun5381]

Thanks a lot Ajax for your prompt reply, there are small correction required, if Single user is online then this Query will not giving any result because this query not considering Last online user, below is the modified Query......Once again thank you very so much...Great Job...

SELECT T.*, O.Online_Users_Name
FROM tblTaskDetails AS T, tblOnlineUsers AS O
WHERE (((T.Unallocated)=True) AND ((O.Online)=True) AND (((SELECT Count(*) FROM tblOnlineUsers WHERE Online=True AND Online_Users_Name<O.Online_Users_Name))=(SELECT Count(*) FROM tblTaskDetails WHERE Unallocated=True AND IncidentDate<=T.IncidentDate) Mod (SELECT Count(*) FROM tblOnlineUsers WHERE Online=True)))
ORDER BY T.IncidentDate;

[CJ_London]

well - I did say 'something like'