Hi,
I am not that expert in field but learning every day. I am sorry I can not post real database or its record or user information due to privacy reasons. But below I made sample.
What I have:
front end backend splitted, multiuser login database. Now want add new functionality in it.
Duties management solution:
users table
Duties Type table
Duties detail table
User can create duties, then admins (from those users) can assign duties to some one else, if that some one is not available then he can transfer his to some one else.
Marked highlighted are users in the table below.....
For example,
I am user "D" was assigned some duties by admin user, so those are my own duties,
but on the top of it some one else transferred his duty to me as well, So how can I calculate all of my duties now? Detail below will help, I am sorry for this long explanation, but I could not manage it to make it short. Please dont be angry, reply only if like to. Thank you in advance.
To combine duties of a user from Assign to and TransferredTo
Query1 to calculate duties for user (under assigned to)
I made Query1 containg all these fields in above table: with extra fields as below to calculate total
-NumberOfAssignedDuties: IIf([Assigned]="Yes",1,0) >> this column tells me number of assigned duties
-PendingDuties: IIf([Assigned]="No",1,0) >> this tells me pending duties -- which are not asigned yet.
-TotalStarted: IIf([Performance]="Started",1,0) >> this tells me total started duties
-TotalCompleted: IIf([perfomance]="completed",1,0) >> completed
-TotalTranstoSomeone: IIf([Transferred]="completed",1,0) >> transferred to someone
Query2 is sum up, those calculated field from query1, and ID as count, Users under AssignedTo as Group
this way I got total for each users "AssingedTo" , Now I know, who has completed how many, Who has how many transferred to some one etc etc
Query3 :: to calculate transferredTo , meaning for example How many are trasnferred to me (as user D in above)
This query again calculated above formula, but containing only three fields,
{TransferredTo}, {performance}, TotalTranstoMe: IIf([Transferred]="completed",1,0)
Idea behind is to calculate how many are transferred to me "for example "D" as above table"
Problem:
I want to calculated total duties of D, whether these are of his own or were transferred to him from some one.
I tried to make a query4: from above query Query2 +Query3, but because both of these have users with different field name "AssignedTo" and "TransferredToMe" respectively.
For this purpose in query window, I linked these fields to user table, and dragged user field into query along with other calculated fields from Query1 and Query2.
But this it shows only common users in Query1 and Query2 but not others.
Question: How can I calculated total duties assigned to user "D" and "B" in above, because some of duties are their own and some are transferred to them
How can I add duties transferred to them?
I am very thankful for reading this detail, may be solution is very simple but I could not solve it.
I am not that expert in field but learning every day. I am sorry I can not post real database or its record or user information due to privacy reasons. But below I made sample.
What I have:
front end backend splitted, multiuser login database. Now want add new functionality in it.
Duties management solution:
users table
Duties Type table
Duties detail table
User can create duties, then admins (from those users) can assign duties to some one else, if that some one is not available then he can transfer his to some one else.
Marked highlighted are users in the table below.....
For example,
I am user "D" was assigned some duties by admin user, so those are my own duties,
but on the top of it some one else transferred his duty to me as well, So how can I calculate all of my duties now? Detail below will help, I am sorry for this long explanation, but I could not manage it to make it short. Please dont be angry, reply only if like to. Thank you in advance.
To combine duties of a user from Assign to and TransferredTo
Query1 to calculate duties for user (under assigned to)
I made Query1 containg all these fields in above table: with extra fields as below to calculate total
-NumberOfAssignedDuties: IIf([Assigned]="Yes",1,0) >> this column tells me number of assigned duties
-PendingDuties: IIf([Assigned]="No",1,0) >> this tells me pending duties -- which are not asigned yet.
-TotalStarted: IIf([Performance]="Started",1,0) >> this tells me total started duties
-TotalCompleted: IIf([perfomance]="completed",1,0) >> completed
-TotalTranstoSomeone: IIf([Transferred]="completed",1,0) >> transferred to someone
Query2 is sum up, those calculated field from query1, and ID as count, Users under AssignedTo as Group
this way I got total for each users "AssingedTo" , Now I know, who has completed how many, Who has how many transferred to some one etc etc
Query3 :: to calculate transferredTo , meaning for example How many are trasnferred to me (as user D in above)
This query again calculated above formula, but containing only three fields,
{TransferredTo}, {performance}, TotalTranstoMe: IIf([Transferred]="completed",1,0)
Idea behind is to calculate how many are transferred to me "for example "D" as above table"
Problem:
I want to calculated total duties of D, whether these are of his own or were transferred to him from some one.
I tried to make a query4: from above query Query2 +Query3, but because both of these have users with different field name "AssignedTo" and "TransferredToMe" respectively.
For this purpose in query window, I linked these fields to user table, and dragged user field into query along with other calculated fields from Query1 and Query2.
But this it shows only common users in Query1 and Query2 but not others.
Question: How can I calculated total duties assigned to user "D" and "B" in above, because some of duties are their own and some are transferred to them
How can I add duties transferred to them?
I am very thankful for reading this detail, may be solution is very simple but I could not solve it.