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]calculated fields

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
geoff_paul
General User
General User


Joined: 11 May 2009
Posts: 11
Location: United Kingdom

PostPosted: Sat May 16, 2009 4:07 am    Post subject: [SOLVED]calculated fields Reply with quote

I am in the process of migrating from Lotus Approach. I need some help please. My question is: Is it possible in a view or query to create calculated columns based on other calculated or aggregated columns? I have a database of all the holidays we have ever been on. There are two tables "basic_info" and "spend_data", the link field is "start_date", there is a one to many relationship between them.

In my final summary view I have a column "full_price" which is an addition of fields from "basic_info" and "total_spending" which is a summed value of "amount" from "spend_data", the view is grouped by "start_date".

I need to add "full_price" and "total_spending" together to yield "total_cost" and to divide this by "length" to give "total_cost_per_day".

This last step is defeating me. Any ideas please. I realise I can put the view into Calc and do it there but this is a cop out and not convenient or elegant.

Thanks in anticipation Geoff Paul


Last edited by geoff_paul on Wed Jun 03, 2009 8:42 am; edited 1 time in total
Back to top
View user's profile Send private message
big_cheez
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2009
Posts: 123

PostPosted: Sat May 16, 2009 7:24 am    Post subject: Re: calculated fields Reply with quote

Sorry, I couldnt follow what fields were where but here goes ...

If your querying only only table here is the basic syntax:
SELECT "field1" + "field2" AS "result1", ("field1" + "field2") / "field3" AS "result2" FROM "table"

If you dealing with two tables you need to add more to the query:
SELECT "table1"."field1" + "table2"."field2" AS "result1", ("table1"."field1" + "table2"."field2") / "table1"."field3" AS "result2" FROM "table1", "table2"

clear as mud?
Back to top
View user's profile Send private message
geoff_paul
General User
General User


Joined: 11 May 2009
Posts: 11
Location: United Kingdom

PostPosted: Sat May 16, 2009 8:47 am    Post subject: calculated fields Reply with quote

Sorry if it wasn't clear. "full_price" is generated in "prices_VEW, it is an alias for ("basic_price"+"extras"+"pre_buy_trips") all from the "basic_info" table. "total_spend" is generated in "full_cst_sumry_VEW" Grouped by "start_date" and is an alias for the SUM of "amount" from the "spend_data" table.
GP
Back to top
View user's profile Send private message
big_cheez
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2009
Posts: 123

PostPosted: Sat May 16, 2009 9:13 am    Post subject: Reply with quote

not sure if you saw my original reply before i edited it but ...
the above query format should work (without trying to exactly match your needs)
change them as needed, reference the views & queries same as tables
does this answer things?

Confused my head is beginning to hurt Smile
Back to top
View user's profile Send private message
geoff_paul
General User
General User


Joined: 11 May 2009
Posts: 11
Location: United Kingdom

PostPosted: Sun May 17, 2009 2:19 am    Post subject: Reply with quote

Hi Big-cheez, somehow I have got a bit out of phase with replies but I thank you for your interest.
To explain my problem using your example the question becomes how does one generate say "("Result1" + "Result2") and further (("Result1" + "Result2")/"table1"."fieldx")?
Geoff Paul
Back to top
View user's profile Send private message
big_cheez
OOo Enthusiast
OOo Enthusiast


Joined: 28 Mar 2009
Posts: 123

PostPosted: Sun May 17, 2009 4:38 am    Post subject: Reply with quote

geoff_paul wrote:
Hi Big-cheez, somehow I have got a bit out of phase with replies but I thank you for your interest.
My fault for the sync problem, edited my original reply, you saw first version.
geoff_paul wrote:

To explain my problem using your example the question becomes how does one generate say "("Result1" + "Result2") and further (("Result1" + "Result2")/"table1"."fieldx")?
Are you creating one query, then building another query off that, or will this all be in a single query?
Back to top
View user's profile Send private message
geoff_paul
General User
General User


Joined: 11 May 2009
Posts: 11
Location: United Kingdom

PostPosted: Sun May 17, 2009 6:27 am    Post subject: Reply with quote

What I have is:
2 tables - "basic_info" and "spend_data"
and
4 Views - (1) "b_i_chrono", (2) "prics_VEW", (3) "spending_VEW"
and (4) "final_cst_sumry"
Back to top
View user's profile Send private message
geoff_paul
General User
General User


Joined: 11 May 2009
Posts: 11
Location: United Kingdom

PostPosted: Thu May 28, 2009 2:39 am    Post subject: [SOLVED]calculated fields Reply with quote

Problem solved! My thanks go to "slide-rule" for the solution. In the Base Thread "Calculated Fields and If-Then-Else" there was the following:
"[quote="sliderule"]kcheek:

What you have said in the above post, is all true . . . at least . . . to the best of my knowledge.

Now, if it were me ( infamous last words ) . . . what I would do, is:

Create a VIEW . . . that is . . . a VIEW where you have 'calculated' your average . . . including the Primary Key to your table.

This way, you could 'link' the VIEW and your TABLE in a query. So, you will have available, from the VIEW, the 'calculated average' as a field, so, you can than perform your additional calculations, without having to 're-calculate' the average multiple times.

I hope that helps, and, Welcome to the Universe of OpenOffice Base. Smile"

This is exactly what I did and it worked, however It would be a boon if there was a field type "calculated" - any hopes?

Thanks again

Geoff Paul
Back to top
View user's profile Send private message
geoff_paul
General User
General User


Joined: 11 May 2009
Posts: 11
Location: United Kingdom

PostPosted: Sat May 30, 2009 6:41 am    Post subject: [SOLVED]calculated fields Reply with quote

Ihave solved my problems with this topic. See previous post.
Geoff Paul
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
Page 1 of 1

 
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