(SOLVED) Further calculations from calculated fields in Base

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:

S/H Paid
“BuyPrice” + “S/H Paid” + “TaxPaid”

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

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

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”)

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

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

20 points required