Link to home
Start Free TrialLog in
Avatar of verpit
verpitFlag for United States of America

asked on

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
Avatar of Sheils
Sheils
Flag of Australia image

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
Avatar of verpit

ASKER

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.
Avatar of verpit

ASKER

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.
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.
Avatar of verpit

ASKER

Notice the different instance of the same table in the sub-query.    

That concerns me.  How can I fix that?
Avatar of verpit

ASKER

follow-up question while putting this one on hold for sake of 'marker'.  re: defining a new calculation.
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?
Avatar of verpit

ASKER

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.
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?
Avatar of verpit

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Still learning how to use the snippets;-)
Avatar of verpit

ASKER

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.
Avatar of verpit

ASKER

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.
Avatar of verpit

ASKER

closed in interest of not getting off-topic... will return in new question.
Avatar of verpit

ASKER

test comment
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
Avatar of verpit

ASKER

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?
Avatar of verpit

ASKER

here is a complete listing.

2.png
Avatar of verpit

ASKER

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.
Avatar of verpit

ASKER

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.
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!
Avatar of verpit

ASKER

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.