Go Premium for a chance to win a PS4. Enter to Win

x
Solved

How is a this query field calculated?

Posted on 2008-10-05
Medium Priority
386 Views
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?

0
Question by:verpit
• 15
• 7

LVL 16

Expert Comment

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

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

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

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

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

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

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

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

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

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

GRayL earned 1500 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:

Min(c.ActivateDT) As FirstActivation,
Max(c.DeactivateDT) AS LastDeactivation,
FROM qryActDeact AS c GROUP BY c.TaskID;
0

LVL 44

Expert Comment

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

Author Comment

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

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 ??

I would really appreciate your assistance here.
0

Author Closing Comment

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

Author Comment

ID: 22671774
test comment
0

LVL 44

Expert Comment

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

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

ID: 22674748
here is a complete listing.

2.png
0

Author Comment

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

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

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

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

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
Suggested Courses
Course of the Month9 days, 18 hours left to enroll