Solved

How is a this query field calculated?

Posted on 2008-10-05
23
365 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:verpit
  • 15
  • 7
23 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 22644066
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
 

Author Comment

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

Author Comment

by:verpit
ID: 22644355
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22644535
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
 

Author Comment

by:verpit
ID: 22644708
Notice the different instance of the same table in the sub-query.    

That concerns me.  How can I fix that?
0
 

Author Comment

by:verpit
ID: 22644740
follow-up question while putting this one on hold for sake of 'marker'.  re: defining a new calculation.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22645346
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
 

Author Comment

by:verpit
ID: 22645397
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22645469
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
 

Author Comment

by:verpit
ID: 22645502
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
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 22645586
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 44

Expert Comment

by:GRayL
ID: 22645604
Still learning how to use the snippets;-)
0
 

Author Comment

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

Author Comment

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

Author Closing Comment

by:verpit
ID: 31503156
closed in interest of not getting off-topic... will return in new question.
0
 

Author Comment

by:verpit
ID: 22671774
test comment
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22673609
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
 

Author Comment

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

Author Comment

by:verpit
ID: 22674748
here is a complete listing.

2.png
0
 

Author Comment

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

Author Comment

by:verpit
ID: 22674837
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22674863
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
 

Author Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 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

20 Experts available now in Live!

Get 1:1 Help Now