[Solved]Alias

[ETobias]

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?

[Sliderule]

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

[ETobias]

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.

[ETobias]

I was unable to figure out how to add [Solve] in the original "Subject" line. Please point me the the right direction. Thx.

[ETobias]

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.

[Sliderule]

ETobias:

You said:

[ETobias]

Thanks for your suggestion. It appears to be fairly complicated so I will have to study a little.

[ETobias]

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

[Sliderule]

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

[ETobias]

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

[Sliderule]

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

[ETobias]

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.

[Sliderule]

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

Conclusion: You now have available for you in your Query . . . the 'pieces', with Alias names, that you wanted for any 'further' calculations.

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.

[ETobias]

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

[ETobias]

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"]