Solved

difficult query calculation (tweak needed)

Posted on 2008-10-06
31
306 Views
Last Modified: 2013-11-27
The latest revision is at http://www.dropio.com/tasksdb.  I am in need of a better calculation or elimination of unneeded values.  The qryCalcActive is supposed to note the time span for each 'period of activity' in a field called ActiveTimes which it does do.  The problem is that it also reports other data as active when it really isn't.  Hnasr states that Active=False "somewhere?" should fix it.  I'm going to look for this next.  Much thanks to anyone who can take a look at the database and chime in.
0
Comment
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
  • 19
  • 8
  • 3
31 Comments
 

Author Comment

by:verpit
ID: 22650194
Added the criteria to qryCalcActive field ActiveTimes as Active=False seems empty the query.  What could I be doing wrong?
0
 

Author Comment

by:verpit
ID: 22650462
some renames...  qryCalcActiveDurations and now MinutesActive that is not reporting minutes active but also some unecessary minutes.  Help requested.   update posted http://www.dropio.com/tasksdb
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22659104
OK, I'm looking at qryCalcActiveDurations.

Help me zero in on the problem.  the results are shown in the attached file.

What should they look like instead?

Thanks.

   


qryCalcActiveDurations-Results.GIF
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

Author Comment

by:verpit
ID: 22659260
It's difficult to tell because I am not sure what you left active and for how long.  
If you clear the records in the tables, perform the following.
Leave only one item open for say 5 minutes or so.
Then, deactivate it by unchecking the active check box in frmAddTasks
You will notice that the day field of the frmAddTasks itself now shows No Active Task!
This is what you want.  
Now you should be able to look at qryActiveDurations and see some values that should not be there.  Ideally, if only one task and one duration was in active status for 5 minutes, then only that task and that duration should show the value.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22659344
I downloaded the DB as you posted it and changed no data.

Would something in it be changing the data just by virtue of opening the DB?
0
 

Author Comment

by:verpit
ID: 22659407
Not that I know of....  I just clear the records in the two main tables and run a task for 5 minutes through frmAddTasks.  I then make that task inactive and check the query.  Should only be one number 5.  I'm doing it again now to try and nail down the specifics.
0
 

Author Comment

by:verpit
ID: 22659699
This is very odd!  In the database I just uploaded, I was about to write saying I made a mistake and that it works perfectly.  Notice in the query, that it now shows 18 values for nearly every task at at least one point.  I started clicking them at random, which has the effect of activating them and deactivation whatever came before.  I clicked done on a few and reactivated some of them randomly.  Nothing has been open for more than 15 minutes (including the pear which has an extra 3 minutes added).
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22661734
So is the problem believed to be in the CAPTURE of the data, or the REPORTING of the data?

I was looking at the reporting side ...
0
 

Author Comment

by:verpit
ID: 22661860
I think it could be in the CAPTURE of the data.  This thread here denotes some changes to the table that GrayL is saying need to be fixed first.   Can you read it here http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Architecture-Design/Q_23791933.html?cid=239#a22661151 and give me your perspective?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22664568
Modified version taking seconds into account, check and report!

database-v1-3-5c-x.mdb
0
 

Author Comment

by:verpit
ID: 22665106
That's excellent!

Version uploaded as version 5e.  It looks like it calculates correctly and I can't get the query to do anything other than behave perfectly!

Interestingly, it also calculates time that was 'inactive' which is not desired.  Perhaps that time could be shown as a 0 or something?  Also, the time in the query should perhaps report in format min:sec .  This will make for an easier comparison down the road with reporting and with timer repair.

What do you think?


0
 
LVL 30

Expert Comment

by:hnasr
ID: 22665238
<Interestingly, it also calculates time that was 'inactive' which is not desired>
Checking
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22665279
Are you talking about calculations in the query?
We are excluding that in the query that is used in the timing "qryCalcLifeSpan"
"qryCalcActiveDurations" query is intermediate and you can nest it later in the used query. But do that in a tidy up stage.
0
 

Author Comment

by:verpit
ID: 22665292
I'd rather get the query right first.  I see seconds for inactivity which is undesired.  The desire is to see only "active" seconds and preferably represented in min:sec
0
 

Author Comment

by:verpit
ID: 22665305
I may not be following you entirely.  If qryCalcActiveDurations is intermediate, then the goal would be to report the totals in qryCalcLifeSpan, correct?  This is great!

Even so, the periods of 'inactivity' should show as zeros and the reporting should still be min:sec.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22665389
0
 

Author Comment

by:verpit
ID: 22665462
Looks better....  How to represent in min:sec ?
0
 

Author Comment

by:verpit
ID: 22665491
I spoke too soon..  Version F uploaded.   The longest task is the one named "second task" which was open for over 3 minutes and shows zero's in every active slot.  This is partially why it needs to show min:sec so that I can more easily troubleshoot.
0
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 22665554
<Looks better....  How to represent in min:sec ?>
Try this: Done in the intermediate query, you can do the same in the final query.

database-v1-3-5e-x2.mdb
0
 

Author Comment

by:verpit
ID: 22665655
OK... thanks for the min:sec calculation...  this helps.

Notice the screenshot.  I have uploaded the database here (version 5g) so you can see it in action.  There is still a problem somewhere?

1.png
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22665779
I see that, and it is because of completing the task then reactivating!
Looking into that!
0
 

Author Comment

by:verpit
ID: 22665798
you are amazing!


thank you for hanging in there...
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22666208
Looks easy, but may not.  
The idea is not to have two consquative inactives. This situation happens if task b is active, and you activate task a then task b becomes inactive, when clicking complete of task b, it is recorded as inactive for a second time.
Checking later when time allows!
0
 

Author Comment

by:verpit
ID: 22666218
I appreciate it very much....  I'll look forward to your reply.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22668278
Hello again,
Please test this version.
database-v1-3-5g-x.mdb
0
 

Author Comment

by:verpit
ID: 22671909
I can't respond to this post here http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23790442.html?cid=239#a22668278 because there is no submit button when using it from work.  I sent you an email with lenghty detail.  

Here is what I found in short:

1. Unchecked active box shows active time, the good news is that 'inactive time' isn't shown but unchecked is not desired.

2. The done button (which used to move completed items to the bottom of the form no longer works leaving each task always incomplete and in the top of the form.

0
 

Author Comment

by:verpit
ID: 22672500
np
0
 

Author Comment

by:verpit
ID: 22674770
0
 

Author Comment

by:verpit
ID: 22674778
hnasr...  grayl might be on to something here...  What do you think about this?
0
 

Author Closing Comment

by:verpit
ID: 31503421
closed in interest of clarity
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sub form showing data is being saved but cannot be displayed.. 31 62
Question about Common Table Expressions 3 40
T-SQL: Wrong Result 7 35
Excel graph in access report 1 29
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

738 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