How to calculate total in access query (Specific question) (1 Viewer)

Ihk

Member
Local time
Today, 10:18
Joined
Apr 7, 2020
Messages
280
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.
1625599590971.png

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.
 

Attachments

  • 1625599092683.png
    1625599092683.png
    44.8 KB · Views: 442

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Feb 19, 2002
Messages
42,981
I need a better understanding of the workflow.
1. Once a task is assigned to a new person, can it ever be reassigned? The structure does not support that. If you just changed the TransferTo value, you lose the fact that it was once transferred to someone else.
2. Why would you want to count tasks assigned to a person if those tasks have been reassigned? Looks like you are counting task1 for both B and D. Does that make sense?
 

Ihk

Member
Local time
Today, 10:18
Joined
Apr 7, 2020
Messages
280
I need a better understanding of the workflow.
1. Once a task is assigned to a new person, can it ever be reassigned? The structure does not support that. If you just changed the TransferTo value, you lose the fact that it was once transferred to someone else.
2. Why would you want to count tasks assigned to a person if those tasks have been reassigned? Looks like you are counting task1 for both B and D. Does that make sense?
1st of all thank you very much for your reply. Let me explain a bit to understand. Here the tasks are also just examples.
All the tasks will keep on repeating, day to day, week to week.
Calculation idea behind this is, to know How many tasks a person has performed in a day or in a week. Who is more overloaded than the other one.
1) Yes the task can be reassigned to a person (either the same person or different), Why to Transfer? Answer is In case person was assigned as task but can not perform due to some reason (may be sick or what ever), he / she has authority and can transfer the task to his colleague.
In that case, number of transferred tasks will be substracted from his total tasks. and this task should be added up to the user to whom it is transferred.
2) Total assigned tasks are counted, to know how many tasks will he perform. But on the other hand total transferred tasks will be counted to know how many he transferred. So these will be substracted from his total tasks. Till this I was able to calculate, but when any of the task is transferred to to other person. This need to be added into total tasks of that person. this is main issue at the moment.

I have idea to do this, but dont know how can I implement this.
There should be table of task performers (although they are already in user table), and once task is assigned to each person or is transferred to transferred to any person. All these tasks should be added up into account of each person.
Meaning each person should have an account, their assigned tasked and Transferred to them should be added, while transferred to someone should be substracted. But this is just idea, practically have to learn.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Feb 19, 2002
Messages
42,981
I would use the same concept that is used for inventory. In the Transfer process, log the transfer to person but create a new record showing the task assigned to the transferTo person. Use your judgement on what the other values should be.

Then in your counting queries, always ignore records where the TransferTo field is not null. That way you don't have to figure out what to subtract and when. Creating the new task rows should greatly simplify the process AND if it turns out you need to do some analysis of tasks that got transferred, you have the whole chain available since each transfer creates a new record.
 

Ihk

Member
Local time
Today, 10:18
Joined
Apr 7, 2020
Messages
280
I would use the same concept that is used for inventory. In the Transfer process, log the transfer to person but create a new record showing the task assigned to the transferTo person. Use your judgement on what the other values should be.

Then in your counting queries, always ignore records where the TransferTo field is not null. That way you don't have to figure out what to subtract and when. Creating the new task rows should greatly simplify the process AND if it turns out you need to do some analysis of tasks that got transferred, you have the whole chain available since each transfer creates a new record.
Actually this is nice and easy to count, when this TransfersTo enters as new record. But because all data is based in one table (except users, tasktype) the rest of the data is in the same table, while tasktype (dutyType) field is required. Thats why 3rd user (Task Performer) can not create new record at least in that table.
In that case should I split table into 3.
1) Where user creates tasks / duties
2) Where Admin assigns duties
3) Task performer Changes the status of tasks, either completed, or transferes etc..
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Feb 19, 2002
Messages
42,981
Actually this is nice and easy to count
But your question indicates that you are having trouble counting. I offered the solution I would use.

I didn't say to split the table, I said to add a new row when a task was transferred. You can do whatever you want when you do it with VBA since you will not be going through the normal BeforeUpdate validation logic.
 

Ihk

Member
Local time
Today, 10:18
Joined
Apr 7, 2020
Messages
280
But your question indicates that you are having trouble counting. I offered the solution I would use.

I didn't say to split the table, I said to add a new row when a task was transferred. You can do whatever you want when you do it with VBA since you will not be going through the normal BeforeUpdate validation logic.
You solution is nice.
Data in table entered in three steps, 1st user, 2nd user, 3rd user.
1st is task creater --- some of these field are set to required.
2nd user, assigns task
3rd users -- either completes or transfers....

This all is in same table.
Problem is , 3rd user while transferring will not be able to create new record as new row, because in each new record Tasktype is required. This is actual problem in the single table , single record will be updated by 3 users. Is there any possibility without splitting the table?
Yes main purpose is counting.
 

Ihk

Member
Local time
Today, 10:18
Joined
Apr 7, 2020
Messages
280
You solution is nice.
Data in table entered in three steps, 1st user, 2nd user, 3rd user.
1st is task creater --- some of these field are set to required.
2nd user, assigns task
3rd users -- either completes or transfers....

This all is in same table.
Problem is , 3rd user while transferring will not be able to create new record as new row, because in each new record Tasktype is required. This is actual problem in the single table , single record will be updated by 3 users. Is there any possibility without splitting the table?
Yes main purpose is counting.
1625613491491.png

Highlight red is set to required in each record, If on transfer 3rd user (performer) tries to create new record for the same task, then it will be problem. In table new record means new task is must
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Feb 19, 2002
Messages
42,981
I thought the "ID" was the PK. Are you saying that DutyType is the name of the task and it must be unique? If so, you can add a suffix to indicate a transfer. OR you can do all your calculations two ways - by AssignedTo and then by TransferredTo and then you can figure out which queries need the delete logic. Doing everything two ways seems to me to over complicate the process but what do I know?

PS, I personally hate naming all PK's "ID". I much prefer using a meaningful name so when I see a FK I might be able to figure out which table it is pointing to without having to open up the relationship diagram.
 

Users who are viewing this thread

Top Bottom