| View previous topic :: View next topic |
| Author |
Message |
lillypillyotter
General User
Joined: 31 May 2008
Posts: 12
|
 Posted: Fri May 08, 2009 10:17 pm Post subject: Sum Total of Multiple Queries [Solved] |
 |
|
Using Oo Base and its native database I have created a table called "Table123".
On this table I have fields "Start1", "Finish1", "Start2", "Finish2", "Start3" and "Finish3".
From this I have created both using "Table Create View" and "Query Create Query" using the Formula "DATEDIFF( 'minute', "FINISH1", "START1" ) * 0.000694444444444444" to get the result "TIME1" etc and have it display on a Table or Query called "Table1a" and "Table2a" and "Table3a" in Time Format HRS:MIN
Is it possible to get a Resulting Total using the Fields "TIME1", "TIME2" and "TIME3" ?
Thanks
Last edited by lillypillyotter on Sun May 10, 2009 1:39 pm; edited 1 time in total |
|
| Back to top |
|
 |
sliderule
Super User
Joined: 29 May 2004
Posts: 1359
|
| Posted: Sat May 09, 2009 7:18 am Post subject: |
|
|
lillypillyotter:
You asked: | lillypillyotter wrote: | | Is it possible to get a Resulting Total using the Fields "TIME1", "TIME2" and "TIME3" ? |
Since you have a 'calculated' NUMERIC field ( made up of minute from the DATEDIFF function), you can use the SUM function to create an AGGREGATE Query.
That is, take your "TIME1" Column ( field ) and choose the function SUM. You will also have to decide what Column(s) ( field(s) ) you want to GROUP BY . . . that is . . . to GROUP BY what 'distinct' column.
Please see the link below, with a GROUP BY, DATEDIFF by minute example . . . with graphics. 
http://www.oooforum.org/forum/viewtopic.phtml?t=66790
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
lillypillyotter
General User
Joined: 31 May 2008
Posts: 12
|
| Posted: Sat May 09, 2009 4:15 pm Post subject: |
|
|
Thanks
I can see what you are saying in your reply but it appears to me from the graphics that you are getting a result by going
Date1 Time1 Time2 Time3 (1 Record)
Date2 Time1 Time2 Time3
Total1 Total2 Total3
Where as what I want is
Date1 Time1 Time2 Time3 Total(Time1+Time2 + Time3) (same record)
Could you please explain for me a little more
Thanks |
|
| Back to top |
|
|
sliderule
Super User
Joined: 29 May 2004
Posts: 1359
|
| Posted: Sat May 09, 2009 4:31 pm Post subject: |
|
|
lillypillyotter:
I am not 100% certain I understand exactly what you want. Tell me if this is correct: You want to ADD ( calculate ) in minutes, each 'result'. - The total of minutes of the three 'set' of values for each record in your 'result set' per the Criterion line:
| Code: | | DATEDIFF('minute',"FINISH1","START1") + DATEDIFF('minute',"FINISH2","START2") + DATEDIFF('minute',"FINISH3","START3") |
The total of hours of the three 'set' of values ( because dividing by 60 ) for each record in your 'result set' per the Criterion line:
| Code: | | ( DATEDIFF('minute',"FINISH1","START1") + DATEDIFF('minute',"FINISH2","START2") + DATEDIFF('minute',"FINISH3","START3" ) ) / 60.0000 |
Does that hope? I hope so, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
lillypillyotter
General User
Joined: 31 May 2008
Posts: 12
|
| Posted: Sat May 09, 2009 6:00 pm Post subject: |
|
|
Ah!, I see now. We are both on the same wavelength now.
I was trying to do it by taking the individual results and totaling them.
I must go out now so I will try this later when I get back and if it works I will then mark it as solved
Thanks |
|
| Back to top |
|
|
|
