We help IT Professionals succeed at work.

difficult query calculation

verpit
verpit asked
on
455 Views
Last Modified: 2013-11-28
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, then the task has been made active.  If the active box there is unchecked, then the same task has been deactivated.  This happens any number of times for each task.  I need to determine active time for each task based on the values in that table, for each time it they were made active/inactive, and sum those differences.  I would prefer to display those sums for each task in the existing qryCalcActiveDuration if possible, and in a separate field named "SumOfActiveTimes" with the results preferably displayed in minutes.  That is to say that to obtain the calculation, the following is needed:

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
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Verpit, I don't have MS Access installed on this machine; however, I can connect to the MDB via Visual Studio and from what I can see you have qryCalcActiveDuration with the ActiveForHrs already for each task.  Therefore, to get minutes or seconds you can just add calculation on query.
SELECT        Task, FirstActivation, LastDeactivation, ActiveForHrs, ActiveForHrs * 60 AS ActiveForMins, ActiveForHrs * 3600 AS ActiveForSecs
FROM            qryCalcActiveDuration

Open in new window

Author

Commented:
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.

Author

Commented:
I should say sum of differences or something.  The original question is actually worded better at the top.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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!

Author

Commented:
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?

Author

Commented:
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.

Author

Commented:
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?

Author

Commented:
On hold until issue with table is resolved here

Author

Commented:
argh....   thread here
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
<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"

Author

Commented:
What do you think about that last linked thread?  Is there something to fix there?
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
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.
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
:) 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.  

Author

Commented:
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.

Author

Commented:
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?

Author

Commented:
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
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
My comment posted with attachment needs  revision. An error to be corrected. Back later.
Retired IT Professional
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?  

Author

Commented:
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.

Author

Commented:
follow-up question posted here
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
.<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.
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
<I'm still getting values where there should be none.>
Where? attach a snapshot!

Author

Commented:
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/Programming/Languages/SQL_Syntax/Q_23790442.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.

Author

Commented:
What do you think of this behavior?
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
I'll check!
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Checked and replied in the follow up link!
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.