[SOLVED]calculated fields

[geoff_paul]

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

[big_cheez]

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?

[geoff_paul]

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

[big_cheez]

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?

my head is beginning to hurt

[geoff_paul]

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

[big_cheez]

[geoff_paul]

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"

[geoff_paul]

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

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

[geoff_paul]

Ihave solved my problems with this topic. See previous post.
Geoff Paul