OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved]Alias
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Fri May 21, 2010 7:53 am    Post subject: [Solved]Alias Reply with quote

Because of the calculation being so involved I want to break it down into two calculations. I am trying to use the answer of the first calculation as the starting point of the second calculation. I refer to the first calculation as an Alias but in the second calculation it said unknown field. Any suggestions?

Last edited by ETobias on Tue May 25, 2010 9:55 am; edited 1 time in total
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri May 21, 2010 8:19 am    Post subject: Reply with quote

ETobias:

Please note the links below . . . where you have asked questions . . . I have taken the time to respond . . . with detailed explanations . . . asking that you let us know if that answered your question, AND, asking that you mark them as [Solved] in the first post title, to help OTHERS searching the forum:

  1. http://www.oooforum.org/forum/viewtopic.phtml?t=100936

  2. http://www.oooforum.org/forum/viewtopic.phtml?t=100895

Since you have elected not to take the time to respond, I see no reason to spend any more time to respond to your questions.

Sliderule
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Fri May 21, 2010 9:02 am    Post subject: Reply with quote

I did not realize that you are supposed to mark it down as "Solved", but it does make sense. I will do that. Thanks for the heads-up. My only problem with OpenOffice is that there is very little documentation on its use.
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Fri May 21, 2010 9:09 am    Post subject: Reply with quote

I was unable to figure out how to add [Solve] in the original "Subject" line. Please point me the the right direction. Thx.
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Fri May 21, 2010 9:18 am    Post subject: Reply with quote

I figured how to add(edit) [Solved]. The second advice was definitely helpful and it worked. The first one I am not sure that it got me where I wanted to be. On theory it made sense but it did not solve my problem.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri May 21, 2010 1:07 pm    Post subject: Reply with quote

ETobias:

You said:
ETobias wrote:
The first one I am not sure that it got me where I wanted to be. On theory it made sense but it did not solve my problem.

I do NOT wish to answer that question here, since it is NOT the topic of this forum post. Since I do not know what you did, and, you have not said what you did, I cannot comment. If you wish to 'explain' your actions, step by step in that forum post, I would be willing to help there.

You asked:

ETobias wrote:
Because of the calculation being so involved I want to break it down into two calculations. I am trying to use the answer of the first calculation as the starting point of the second calculation.

The answer is . . . it canNOT be done, exactly the way you want. But, there is a 'solution' or 'workaround'.

Solution: Since you say, you have "calculation being so involved" . . . what you can do is create a View . This View would be made up of your Table Primary Key, and, the calculations ( two or more pieces ) you want. Next, you can create your Query, using BOTH the 'original' Table(s) AND the View with the calculation. By 'linking' the two ( the Table and the View ) based on the Primary Key field(s) . . . you will be able to 'reference' the Alias names from the View as you originally described. Smile

Check out:
http://www.oooforum.org/forum/viewtopic.phtml?t=96140

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
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Fri May 21, 2010 4:02 pm    Post subject: Reply with quote

Thanks for your suggestion. It appears to be fairly complicated so I will have to study a little.
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Fri May 21, 2010 4:06 pm    Post subject: Reply with quote

The reason I want two calculations because it gets very complicated with one. I want to do the following:

Calculation 1 -- Convert the time into decimal number and divide it by total mileage- Answer will be minutes/mile in decimal.

Calculation 2 - Convert the decimal number back into time, i.e., 10.67 into 10:40
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri May 21, 2010 4:56 pm    Post subject: Reply with quote

Suggestion . . .if you want . . . perhaps I can help.

Please enter here
  1. The CODE ( field names and exact calculations ) you are using.
  2. Tell me your TABLE name(s)
  3. Tell me the Primary Key Field name(s)
Sliderule
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Mon May 24, 2010 4:29 am    Post subject: Reply with quote

Table = Runlog
ID (Primary Key)
Date = Date[DATE]
MilesofRun = Decimal[DECIMAL]
TimeofRun = Time[TIME] -- '876613:37:46'

Query 1 (OK)
( ( SECOND( "TimeofRun" ) * 1.E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 1.E0 * 60 ) ) / "MilesofRun"

Alias = PaceofRun

Query 2 ???
To convert the result (decimal) from the Query 1 into time ("Stopwatch").
I tried to use "PaceofRun" (Alias) from Query 1 but Query 2 did not understand what it is.

Eric
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon May 24, 2010 10:09 am    Post subject: Reply with quote

Eric:

I do NOT understand exactly what you asked above.

  1. I think I understand what you called, "Query 1" . . . "PaceofRun". But, I would appreciate, and, I should have asked before, if you can give me some SAMPLE data. Perhaps, 5 ( give or take ) 'records' with actual values, and, the expected result.

    This way, I can 'test' it to confirm I am doing things exactly as you want.

  2. About what you are calling, "Query 2", I do not have any idea what calculation you want performed. Can you attempt to explain it better, with sample data?

Sliderule
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Mon May 24, 2010 10:46 am    Post subject: Reply with quote

Any data would be a good test. Below is what I used.

ID - Automatic
Date - 5/18/10
MilesofRun - 3.8
TimeofRun: 40:04

Quiry 1 gave me an answer of 10.49, which does make sense to me.

Quiry 2 I tried to conver the 10.49 (Decimal) to 10:xx ("Stopwatch" time)
My thought was to take "PaceofRun" (Alias) of Quiry 1 and do the following:

"PaceofRun" Minus RoundDown (PaceofRun)== ??? (Just the decimal value, i.e., .49)
(An OpenOffice function?)

Then take the result and multiply it my 60 (60 seconds/min)

And finally take the above result and add it to RoundDown.

Because Base did not understand the Alias from Quiry 1 I was stuck.

I hope that the above makes sense.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon May 24, 2010 12:48 pm    Post subject: Reply with quote

Eric:

Just as an FYI . . . using your 'sample data' I am calculating it as . . . 10.54386 . . . rather than 10.49 as you said.

OK, need to create the View - "Runlog_PaceofRun_View" - . . . which will calculate:
  1. "PaceofRun"
  2. "Integer_PaceofRun"
  3. "Decimal_PaceofRun"

So, please follow these steps, to create the View.
  1. Open your OpenOffice Database file ( *.odb )
  2. On the LEFT, press the Tables icon
  3. Copy to your Clipboard, the following code:

    Code:
    CREATE VIEW "Runlog_PaceofRun_View" AS
    SELECT
       "ID",
       ( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun" as "PaceofRun",
       FLOOR(( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun") as "Integer_PaceofRun",
       ( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun" - FLOOR(( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun") as "Decimal_PaceofRun"

    FROM "Runlog"

  4. From the Menu: Tools -> SQL...
  5. Under Command to execute, Paste the code above from your clipboard
  6. Press the Execute button
  7. Status should show: 1: Command successfully executed.
  8. Press the Close button
  9. From the Menu: View -> Refresh Tables

So, please follow these steps, to create the Query.
  1. On the left, Press the Queries icon
  2. Under Tasks, press . . . Create Query in Design View...
  3. With the Add Table or Query pop-up, select your Table "Runlog" and Add button, do the same for the new View, "Runlog_PaceofRun_View"



  4. Now need to LINK the two . . . click on the field ID in "Runlog", holding down your mouse, drag it to ID in the view "Runlog_PaceofRun_View"



  5. Add the Fields you want to see in your display
  6. Add any other calculations you need
  7. Add any Criterion you want
  8. Add any Sort order you want
  9. Run your Query using F5, OR, Run Query icon on toolbar, OR, from the Menu: Edit -> Run Query
  10. Save your Query Smile
Conclusion: You now have available for you in your Query . . . the 'pieces', with Alias names, that you wanted for any 'further' calculations. Cool

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
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Mon May 24, 2010 2:36 pm    Post subject: Reply with quote

Thank you very much! I appreciate your effort to solve my problem. I will give it a try and let you know how I make out.
Thanks again.

Eric
Back to top
View user's profile Send private message
ETobias
General User
General User


Joined: 13 Apr 2009
Posts: 19

PostPosted: Tue May 25, 2010 8:07 am    Post subject: Reply with quote

I cut and paste the instructions that you gave me and it was unable to find a Table. I did select the Table icon. The error message is below.

1: Table not found in statement [
SELECT
"ID",
( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun" as "PaceofRun",
FLOOR(( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun") as "Integer_PaceofRun",
( ( SECOND( "TimeofRun" ) * 1E0 / 60 ) + MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun" - FLOOR(( ( SECOND( "TimeofRun" ) * 1E0 / 60 )

+ MINUTE( "TimeofRun" ) + ( HOUR( "TimeofRun" ) * 60 ) ) / "MilesofRun") as "Decimal_PaceofRun"

FROM "Runlog"]
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group