difficult query calculation
I need to modify an existing query to contain a difficult calculation.
tblAllTasksEachActivation shows the times of each activation of a given task. If the active box is checked in tblAllTasksEachActivation,
1. Determine the difference in time elapsed between each activation and deactivation for each task.
2. Produce a single sum for each task.
3. Place that sum in a new field in qryCalcActiveDuration, and name it SumOfActiveTimes
4. Display the sum's value in terms of minutes.
Database housed here www.dropio.com/tasksdb
tblAllTasksEachActivation shows the times of each activation of a given task. If the active box is checked in tblAllTasksEachActivation,
1. Determine the difference in time elapsed between each activation and deactivation for each task.
2. Produce a single sum for each task.
3. Place that sum in a new field in qryCalcActiveDuration, and name it SumOfActiveTimes
4. Display the sum's value in terms of minutes.
Database housed here www.dropio.com/tasksdb
ASKER
Yeah,
I think this is one where you will need the actual database to see what I am referring to here. ActiveForHrs is actually misnamed. It's named correctly for the "intended calculation" but the calculation was written by someone else with a minor misunderstanding of the intention and is therefore, slightly inacurate. The field ActiveForHrs should actually be named something like LifeSpan or something as it does not represent the sum of individual calculations that I am actually after for each task but rather only represents the difference between two (2) times of activity (the one at the start and the one at the end). I rather need the sum of each time a task was made active or inactive. I need see those calculated sums for each task.
You make an excellent point though and I hope you are able to look at the database in Access. You sound like you are one of a few that can really help me out here. Thanks very much for chiming in.
I think this is one where you will need the actual database to see what I am referring to here. ActiveForHrs is actually misnamed. It's named correctly for the "intended calculation" but the calculation was written by someone else with a minor misunderstanding of the intention and is therefore, slightly inacurate. The field ActiveForHrs should actually be named something like LifeSpan or something as it does not represent the sum of individual calculations that I am actually after for each task but rather only represents the difference between two (2) times of activity (the one at the start and the one at the end). I rather need the sum of each time a task was made active or inactive. I need see those calculated sums for each task.
You make an excellent point though and I hope you are able to look at the database in Access. You sound like you are one of a few that can really help me out here. Thanks very much for chiming in.
ASKER
I should say sum of differences or something. The original question is actually worded better at the top.
Maybe that was me AND since I don't have access loaded right now is probably causing my misunderstanding, so you can wait and see if one of the Access experts takes a look. If they do not, since now the question has posts in a reasonable time then you can post a pointer to this question (NOT a double post, but a pointer).
For help on how to do that please refer to the help page http:help.jsp.
For help on how to do that please refer to the help page http:help.jsp.
verpit,
Try this! An extra query added to calculate differences between each activation and deactivation,
Last Active is excluded!
Then your query modified to give the sumOfActiveTimes!
database-v1-3-4s-x2.mdb
Try this! An extra query added to calculate differences between each activation and deactivation,
Last Active is excluded!
Then your query modified to give the sumOfActiveTimes!
database-v1-3-4s-x2.mdb
ASKER
thank you thank you! hnasr... It looks like you used an earlier version so I just imported the query which seemed to work. Was this OK?
new version is version T but lots of email so we will see where it ends up here.
I'm leaving this open for a short time in order to marker it in case of more version trouble. Thank you again. Always a life saver!
new version is version T but lots of email so we will see where it ends up here.
I'm leaving this open for a short time in order to marker it in case of more version trouble. Thank you again. Always a life saver!
ASKER
Much better query but doesn't calculate the correct values quite yet. I'd like it to keep track of each time a task was marked active from the main form and when it was made inactive. What I noticed is that when I accumulated minutes for any one task, the query would suddenly show minutes for several differently named tasks as well but with similar times. I let one of the tasks live for 8 minutes and got similar values in many tasks.
Then we have the issue of how to sum in the final rptGroupAndTotal but I can ask that question elsewhere. Then after that, it's back to the timer issue the function of which, I have a much better handle on.
The latest database here. What do you think hnasr?
Then we have the issue of how to sum in the final rptGroupAndTotal but I can ask that question elsewhere. Then after that, it's back to the timer issue the function of which, I have a much better handle on.
The latest database here. What do you think hnasr?
ASKER
ps
new query has been renamed qryCalcActiveDuration (same as the last query). The last query was renamed to something more appropriate and moved to the soon to be retired group of objects.
new query has been renamed qryCalcActiveDuration (same as the last query). The last query was renamed to something more appropriate and moved to the soon to be retired group of objects.
ASKER
Argh... It just occured to me that the query may not be needed after all since the tblAllTasksEachActivity already has the times. All I really need are calculations based on those times and summed together for each task name listed once in the new query. That is to say.... "Is the new field ActDeact really necessary?" Your call. Does this make sense?
ASKER
On hold until issue with table is resolved here
ASKER
argh.... thread here
<Much better query but doesn't calculate the correct values quite yet. >
Need time to look at, approach changed from what I was planning!
< ActDeact really necessary?" >
Well this is an alias to the original field "ActivationDateTime" of table "tblAllTasksEachActivity"
Need time to look at, approach changed from what I was planning!
< ActDeact really necessary?" >
Well this is an alias to the original field "ActivationDateTime" of table "tblAllTasksEachActivity"
ASKER
What do you think about that last linked thread? Is there something to fix there?
I prefer separate queries, instead of a complex queriy, while developing a task, it is easier to debug. When all works as planned, then tune that part.
:) I came back to here, following the links.
My approach was to find the total minutes spent for each task using the simple 2 queries. Later to get this amount for the current task, add and to disply the counter.
My approach was to find the total minutes spent for each task using the simple 2 queries. Later to get this amount for the current task, add and to disply the counter.
ASKER
yep... the suggestion here was a redesign by mw for which I am getting serious errors. I'll accept. Let's go back to where we were here and continue with correcting the query design. I'll await your input base on the latest upload. Sorry for the side-track.
ASKER
argh... this is where it should be. I have said mw when I meant someone else in another thread. My head is spinning... back to the query design here in this thread.... What do you think hnasr?
ASKER
It's the totals times each task have been in active status, which could include any number of instances for a particular task, accumulated in terms of minutes that I am after.
task one active for 5 and
task two active for 6 and
task one active for 7
=
task one active for 12
task two active for 6
task one active for 5 and
task two active for 6 and
task one active for 7
=
task one active for 12
task two active for 6
My comment posted with attachment needs revision. An error to be corrected. Back later.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't really care about the timer until the query itself is calculating the correct values and representing in minutes. Once this is verified, we can use the query as a guide to ensure the timer displays accurately as well. Since, you are saying we should now use the qryCalcLifeSpan to build upon, I will remove the other query we talked about previously to clean-up and post another update to reflect that.
latest revision (at least version 4y) updated here http://www.dropio.com/tasksdb
Can you make the change you are suggesting here? What you think?
latest revision (at least version 4y) updated here http://www.dropio.com/tasksdb
Can you make the change you are suggesting here? What you think?
ASKER
4z update uploaded. qryLifeSpan and your qryCalcActive are in place. I don't think I understand the Active=False. Maybe I need to add that? I'm still getting values where there should be none.
ASKER
follow-up question posted here
.<I don't think I understand the Active=False>
Assume these records
task active count
1 x 0
1 - 1 ' was active then became inactive for 1 min, included
1 x 5 ' was inactive then became active after 5 min, this not included
1 - 6 ' was active then became inactive for 6 min, included
and so on
So Active =False takes only the records with the required times.
Assume these records
task active count
1 x 0
1 - 1 ' was active then became inactive for 1 min, included
1 x 5 ' was inactive then became active after 5 min, this not included
1 - 6 ' was active then became inactive for 6 min, included
and so on
So Active =False takes only the records with the required times.
<I'm still getting values where there should be none.>
Where? attach a snapshot!
Where? attach a snapshot!
ASKER
screenshot here www.dropio.com/tasksdb
since this question was accepted (issues such as this remain), see also the follow -up question here https://www.experts-exchange.com/questions/23790442/difficult-query-calculation-tweak-needed.html
In the screenshot, you see 3 items with a number two when there was only one task held open for two minutes. To produce this, I simply cleared the records and started over, leaving only one task open for two minutes.
since this question was accepted (issues such as this remain), see also the follow -up question here https://www.experts-exchange.com/questions/23790442/difficult-query-calculation-tweak-needed.html
In the screenshot, you see 3 items with a number two when there was only one task held open for two minutes. To produce this, I simply cleared the records and started over, leaving only one task open for two minutes.
ASKER
What do you think of this behavior?
I'll check!
Checked and replied in the follow up link!
Open in new window