Ask Your Question

(SOLVED) Further calculations from calculated fields in Base [closed]

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

bdragon254 gravatar image

updated 2020-08-07 03:06:40 +0100

Alex Kemp gravatar image

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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-07 03:06:52.911620



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

1 Answer

Sort by » oldest newest most voted

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

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


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 +0100 )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 +0100 )edit

20 points required

bdragon254 gravatar imagebdragon254 ( 2015-08-24 00:30:01 +0100 )edit

Question Tools

1 follower


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

Seen: 1,369 times

Last updated: Aug 18 '15