Ask Your Question
1

(SOLVED) Further calculations from calculated fields in Base

asked 2015-08-15 11:42:14 +0200

bdragon254 gravatar image

updated 2015-08-18 10:54:23 +0200

Complete newbie to databases as well as SQL.

Calculated fields are quite easy.. eg; "Field Name1" + "Field Name2"

In my database I need further calculations from calculated fields within the same query.

Here is what I have so far:

BuyPrice S/H Paid TaxPaid "BuyPrice" + "S/H Paid" + "TaxPaid"

When the query runs, this outputs to a field with an alias of Total Cost

SellPrice S/H Charged "SellPrice" + "S/H Charged" - ( "SellPrice" * 0.132 + "S/H Charged" * 0.132 )

This outputs to a field with an alias of NET

This is exactly what I need, however I also need a third calculated field for Profit. I cant just enter "NET" - "Total Cost". If I create another query on top of the first one, I can reference the aliases and it works just fine but I need this to all go into the same "Table Control"

Works but it is in two separate Table Control forms

I simply don't know enough about any of this to get it to work.. any help or suggestions would be greatly appreciated

edit retag flag offensive close merge delete

Comments

..........

m.a.riosv gravatar imagem.a.riosv ( 2015-08-18 22:22:51 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2015-08-18 10:58:49 +0200

bdragon254 gravatar image

This was solved by Lyrl on Stackoverflow. I did not realize I would have to write the entire thing in one statement, instead of calculating profit based off of the other calculated fields.

The working SQL is here for anyone that would like to take a look:

"SellPrice" + "S/H Charged" - ( "SellPrice" * 0.132 + "S/H Charged" * 0.132 ) - ("BuyPrice" + "S/H Paid" + "TaxPaid")

edit flag offensive delete link more

Comments

Yes. an alternative way, if that proves ungainly, is to nest your query with the NET and TotalCost aliases into a subquery, and then do your higher-level manipulations in an overarching query, which would be: SELECT "NET", "TotalCost", ("NET" - "TotalCost") As "Profit" FROM (SELECT ("SellPrice" + "S/H Charged" - ( "SellPrice" * 0.132 + "S/H Charged" * 0.132 )) As "NET", ("BuyPrice" + "S/H Paid" + "TaxPaid") As "TotalCost" FROM "Tbl1") As SubQ (or something like that).

doug gravatar imagedoug ( 2015-08-18 14:23:36 +0200 )edit

you can now accept your own answer by clicking the check mark to the left.

doug gravatar imagedoug ( 2015-08-19 03:09:20 +0200 )edit

20 points required

bdragon254 gravatar imagebdragon254 ( 2015-08-24 00:30:01 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-08-15 11:42:14 +0200

Seen: 922 times

Last updated: Aug 18 '15