Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

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, 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
0
verpit
Asked:
verpit
  • 17
  • 11
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
verpitAuthor 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.
0
 
verpitAuthor Commented:
I should say sum of differences or something.  The original question is actually worded better at the top.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
hnasrCommented:
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
0
 
verpitAuthor 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!
0
 
verpitAuthor 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?
0
 
verpitAuthor 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.
0
 
verpitAuthor 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?
0
 
verpitAuthor Commented:
On hold until issue with table is resolved here
0
 
verpitAuthor Commented:
argh....   thread here
0
 
hnasrCommented:
<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"
0
 
verpitAuthor Commented:
What do you think about that last linked thread?  Is there something to fix there?
0
 
hnasrCommented:
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.
0
 
hnasrCommented:
:) 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.  
0
 
verpitAuthor 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.
0
 
verpitAuthor 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?
0
 
verpitAuthor 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
0
 
hnasrCommented:
My comment posted with attachment needs  revision. An error to be corrected. Back later.
0
 
hnasrCommented:
In.v1-3-4w, query qryCalcLifeSpan appears to be working fine for calculating the previous active time.
In my post this equivalent to the second query corrected by adding a criteria Active=False, this eliminates the difference between Active and previous inactive records.

So you may build on the qryCalcLifeSpan  query!
From this point I understand you want the timer to display correctly!
Good luck!
0
 
verpitAuthor 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?  
0
 
verpitAuthor 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.
0
 
verpitAuthor Commented:
follow-up question posted here
0
 
hnasrCommented:
.<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.
0
 
hnasrCommented:
<I'm still getting values where there should be none.>
Where? attach a snapshot!
0
 
verpitAuthor Commented:
screenshot here www.dropio.com/tasksdb

since this question was accepted (issues such as this remain), see also the follow -up question here http://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.
0
 
verpitAuthor Commented:
What do you think of this behavior?
0
 
hnasrCommented:
I'll check!
0
 
hnasrCommented:
Checked and replied in the follow up link!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 17
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now