Solved

difficult query calculation

Posted on 2008-10-05
30
318 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
0
Comment
Question by:verpit
  • 17
  • 11
  • 2
30 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22644957
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
 

Author Comment

by:verpit
ID: 22644998
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
 

Author Comment

by:verpit
ID: 22645004
I should say sum of differences or something.  The original question is actually worded better at the top.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22645096
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
 
LVL 30

Expert Comment

by:hnasr
ID: 22646083
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
 

Author Comment

by:verpit
ID: 22646549
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
 

Author Comment

by:verpit
ID: 22646714
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
 

Author Comment

by:verpit
ID: 22646716
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
 

Author Comment

by:verpit
ID: 22646725
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
 

Author Comment

by:verpit
ID: 22646810
On hold until issue with table is resolved here
0
 

Author Comment

by:verpit
ID: 22646812
argh....   thread here
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22646897
<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
 

Author Comment

by:verpit
ID: 22646913
What do you think about that last linked thread?  Is there something to fix there?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22646912
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
 
LVL 30

Expert Comment

by:hnasr
ID: 22646949
:) 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:verpit
ID: 22646965
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
 

Author Comment

by:verpit
ID: 22646971
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
 

Author Comment

by:verpit
ID: 22646986
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
 
LVL 30

Expert Comment

by:hnasr
ID: 22647124
My comment posted with attachment needs  revision. An error to be corrected. Back later.
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 22647181
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
 

Author Comment

by:verpit
ID: 22647658
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
 

Author Closing Comment

by:verpit
ID: 31503183
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
 

Author Comment

by:verpit
ID: 22650114
follow-up question posted here
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22654066
.<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
 
LVL 30

Expert Comment

by:hnasr
ID: 22654081
<I'm still getting values where there should be none.>
Where? attach a snapshot!
0
 

Author Comment

by:verpit
ID: 22654204
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
 

Author Comment

by:verpit
ID: 22655339
What do you think of this behavior?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22657121
I'll check!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22664650
Checked and replied in the follow up link!
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22664656
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now