How is a this query field calculated?

How is the ActiveForHrs field in qryCalcActiveDuration actually calculated?
I understand that the actual calculation is intended to use both the information in the other two fields (FirstActivation, LastDeactivation) and values from one of the other tables to calculate the ActiveForHrs value.  

I need to verify that:
1. I understand the calculation.
2. What is being calculated is what I want to be calculated.

Where / What is this precise calculation?

Database here http://www.dropio.com/tasksdb
verpitAsked:
Who is Participating?
 
GRayLCommented:
I believe in using short descriptive names.  Now, assuming the data are arranged as per my last post and the errors cleaned up the queries below should give you what you want?  
qryActDeact:
 
SELECT a.TaskID, a.ActDeactDT AS ActivateDT, a.ActDeact, 
(SELECT Min(Nz(b.ActDeactDT,Now())) FROM tblActDeact AS b WHERE b.TaskID = a.TaskID AND b.ActDeactDT > a.ActDeactDT AND NOT b.ActDeact) AS DeactivateDT FROM tblActDeact AS a WHERE a.ActDeact ORDER BY a.TaskID, a.ActDeactDT;
 
This query will give you one line in place of the two for each instance the task was activated and subsequently deactivated.  In the case where the task has not yet been deactivated, it will substitute the value of Now().  Now all that remains is to sum the differences between ActivateDT and DeactivateDT for each task:
 
qryTaskTimes:
 
Select c.TaskID, 
Min(c.ActivateDT) As FirstActivation, 
Max(c.DeactivateDT) AS LastDeactivation, 
Round(Sum(DateDiff("s",c.ActivateDT, c.DeactivateDT)/3600),4) AS HrsTaskActive 
FROM qryActDeact AS c GROUP BY c.TaskID;

Open in new window

0
 
SheilsCommented:
In short it is finding the difference between the other to fields in seconds. Then dividing by 3600 to convert to hours and rounding the answer to 4 decimal places
0
 
verpitAuthor Commented:
Ah OK,

How can I view the actual calculation?

If that is correct, it is not the desired behavior.  The desired behavior is off-topic here and can be questioned in a follow-up.  Just for clarification though...  My desired behavior for that field name ActiveForHrs is the following in brief:

Use both the information in the other two fields (FirstActivation, LastDeactivation) and values from one of the other tables to calculate the ActiveForHrs value.   The purpose of this would be to determine a "total amount of active time" and NOT just the time "span" between the time it was first activated and last became deactivated.  That "span" might be usefull in later calculations though if it could be listed in a different field perhaps named "SpanOfLife".  For my immediate use though, I was hoping to have "ActiveForHrs" add up the individual times of activity and produce a sum of those times.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
verpitAuthor Commented:
Back to my original question in the first post.

Where / What is this 'precise' calculation?  

I need to find the calculation and pick apart the details so that I can formulate the question of how to change what needs to be changed more precisely.
0
 
GRayLCommented:
Open the query in design view, right-click on the big blue bar on top of the query box to take you to the SQL view - presto - the 'precise' calculation.  Notice the different instance of the same table in the sub-query.
0
 
verpitAuthor Commented:
Notice the different instance of the same table in the sub-query.    

That concerns me.  How can I fix that?
0
 
verpitAuthor Commented:
follow-up question while putting this one on hold for sake of 'marker'.  re: defining a new calculation.
0
 
GRayLCommented:
In tblAllTasksEachActivity, with the Task,  Active checkbox and the Datetime, does a record without a checkmark indicate the task was de-activated?  If so, FirstActivation and LastActivation are derivable and not required in a separate table.

What is the purpose of the ActivationDate field, as it is always derivable from the ActivationDateTime field?

When I run the query qryCalcActiveDuration, it says task 1 started on 2008-01-10 without a time, yet the table shows a first activation on 10 Jan 2008 at 18:16:48??

Curiously, task 1 shows two successive final de-activations without an intervening Active check?
0
 
verpitAuthor Commented:
That's very interesting!   I'll do some looking there.  I think I get to do some cleaning up.  Now that I know where to go in specific terms, that should be fun.  The trick will be to make sure I don't break any already existing dependencies.
0
 
GRayLCommented:
Open tblAllTasksEachActivity and move the field ActivationDateTime to the left beside field Task. Now highlight both fields and ckick on SortAscending in the toolbar.  You will now see all task 1 in ascending order by datetime.  Note the two locations of two successive records, both without checks.  That indicates to me an error.  Please confirm.  From the logic in your query, an Active task may not have a deactivation record, in which case use Now() for the query calc - right?

Next I would rename the fields to ActDeactDT (datetime), and ActDeact (checkbox) and delete the ActivationDate field and rename the table tblActDeact.  Task should now be TaskID, a foreign key from the tblTasks - probably autonumber, not text.  Add a first field, autonumber named ActDeactID.  Data like 'Cool', 'Chilling Out', and "Office Work' are more properly TaskDesc and belong in the Tasks table alongside the TaskID.  Comments?
0
 
verpitAuthor Commented:
Paragraph 2 is loosing me but I am hanging this thread around my neck until I understand it.  This is FANTASTIC information!

You said in paragraph 1 "Note the two locations of two successive records, both without checks.  That indicates to me an error.  Please confirm. ".

I noticed that too.  Somehow the null values are tied into all this.  I need to make sure I don't mess up the modules as I am not sure I understand them yet.  They have some significance.

I'll be studying this tonight.  This is wonderful!
0
 
GRayLCommented:
Still learning how to use the snippets;-)
0
 
verpitAuthor Commented:
OK here goes nothing...  latest revision here.

I'm trying now to make the changes you have suggested and have moved the first column over and sorted ascending.  

I am putting all the query stuff on hold here till this is finished.  Thank you for all your help on this.  I'm working away...  please respond if you get a chance to see where it is now.
0
 
verpitAuthor Commented:
I'm running into lot's of relational problems with this.  I'm going to need someone to take this one from me.  It's beyond my skill level (way beyond).  Can I get you to attempt making the necessary changes ??

drop location http://www.dropio.com/tasksdb

I would really appreciate your assistance here.
0
 
verpitAuthor Commented:
closed in interest of not getting off-topic... will return in new question.
0
 
verpitAuthor Commented:
test comment
0
 
GRayLCommented:
Have you noticed the two succesive Active off locations, the first under task 'Under one minute a' and the second 'Under one minute c'.  While they do not affect my calculations, I just wonder why they're there.

I downloaded your most recent mdb and copied tlbtblAllTasksEachActivity to tblActDeact, then went inside and changed the field names as I had in my two queries.  I then created those two queries and after running qryTaskTimes, it gave me the expected values.  I've attached the mdb below
database-v1-3-5g.mdb
0
 
verpitAuthor Commented:
This is excellent work!


Forgive my ignorance though as this adds four (4) additional objects that I don't know how to implement.

How would this replace what I have so that my forms would still function?
0
 
verpitAuthor Commented:
here is a complete listing.

2.png
0
 
verpitAuthor Commented:
Please don't stop....  this is truely excellent..   Assuming I wanted this to be the behavior, please delete what is not needed in my database or any intermediary queries that are no longer needed.  I'll test the forms and then go about the business of finalizing this thing.


This is truely exceptional work that I would not be able to accomplish on my own.
0
 
verpitAuthor Commented:
I may have spoke too soon.  The difference between firstact and lastdeact is not at all the calculation I am after but rather a sum of the diff between "each" activation and deactivation.
0
 
GRayLCommented:
What application is producing 2.png - A2007?  IMHO the my three additions should be assigned to main Objects as that is where all the stuff I used for their creation is.  You seem to be spending a lot of time sweating this detail with little to show for it.  All I did was try to explain the value of concise naming, how to use a query to get the value from the next record, and then use an Aggregate function in a query to get sums, yet for some reason you feel it necessary to fit this stuff into some template like 2.png above and after over a week, you still have not told us about it or why.  Getting miffed!
0
 
verpitAuthor Commented:
I understand and I appreciate your hard work.  I just don't understand what you are telling me.  What I was trying to get out of my post is an answer to the question which I accepted with a grade of A.  I would gladly give you more credit if I could.

If someone developed an extremely complex system that works perfectly "under the hood", I still will have no idea how to use it without an owners manual and a key to turn on the ignition.  It's what you call "sweating the details" that I am unable to accomplish on my own.

I do not know which of my tables I can delete now so that the frmAddTasks and frmGroupAndTotal still perform their desired function.  

You have done the "under the hood" part for which I am eternally grateful!  You are brilliant!  I just don't have a key yet for which to turn it on.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.