Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# difficult query calculation

Posted on 2008-10-05
Medium Priority
380 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
Question by:verpit
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 17
• 11
• 2
30 Comments

LVL 60

Expert Comment

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
``````
0

Author Comment

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

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

LVL 60

Expert Comment

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 31

Expert Comment

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

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

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

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

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

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

Author Comment

ID: 22646812
argh....   thread here
0

LVL 31

Expert Comment

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

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

LVL 31

Expert Comment

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 31

Expert Comment

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

Author Comment

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

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

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 31

Expert Comment

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

LVL 31

Accepted Solution

hnasr earned 1500 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

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

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

ID: 22650114
follow-up question posted here
0

LVL 31

Expert Comment

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 31

Expert Comment

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

Author Comment

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

ID: 22655339
What do you think of this behavior?
0

LVL 31

Expert Comment

ID: 22657121
I'll check!
0

LVL 31

Expert Comment

ID: 22664650
Checked and replied in the follow up link!
0

LVL 31

Expert Comment

ID: 22664656
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 hâ€¦
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as formâ€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
###### Suggested Courses
Course of the Month12 days, 6 hours left to enroll

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

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