Query Calculation using a [Sum of ....] field

[Dave the Wave]

I have a summary query that works great. It sums the various costs of
a sale and gives me a total as deisred. I want to know the profit, so
I try to calculate it by Profit:=[SalePrice]-[SumOfCost].

When I run the query, Access treats the [SumOfCost] like a parameter.
I don't enter a value, just hit enter, and the query calculates the
correct profit answer. Why would access insist the calculated field is
a parameter that needs to be entered?

Here is the SQL of the query:
SELECT DISTINCTROW tblSale.tblSaleID, tblSale.tblListingID,
tblSale.tblSalePrice, tblSale.tblSalePaid, Sum(tblCost.tblCostAmount)
AS SumOftblCostAmount, [tblSalePrice]-[SumOftblCostAmount] AS Profit
FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID =
tblSale.tblListingID) INNER JOIN tblCost ON tblListing.tblListingID =
tblCost.tblListingID
GROUP BY tblSale.tblSaleID, tblSale.tblListingID,
tblSale.tblSalePrice, tblSale.tblSalePaid,
[tblSalePrice]-[SumOftblCostAmount];

ps. I used the "Build.." feature to insure the problem isn't a typo.

Thanks for your time and assistance!
David G.

 

[Marshall Barton]

I have a summary query that works great. It sums the various costs of
a sale and gives me a total as deisred. I want to know the profit, so
I try to calculate it by Profit:=[SalePrice]-[SumOfCost].

When I run the query, Access treats the [SumOfCost] like a parameter.
I don't enter a value, just hit enter, and the query calculates the
correct profit answer. Why would access insist the calculated field is
a parameter that needs to be entered?

Here is the SQL of the query:
SELECT DISTINCTROW tblSale.tblSaleID, tblSale.tblListingID,
tblSale.tblSalePrice, tblSale.tblSalePaid, Sum(tblCost.tblCostAmount)
AS SumOftblCostAmount, [tblSalePrice]-[SumOftblCostAmount] AS Profit
FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID =
tblSale.tblListingID) INNER JOIN tblCost ON tblListing.tblListingID =
tblCost.tblListingID
GROUP BY tblSale.tblSaleID, tblSale.tblListingID,
tblSale.tblSalePrice, tblSale.tblSalePaid,
[tblSalePrice]-[SumOftblCostAmount];

Queries often fail to recognize field alias names in
expressions. Instead repeat the expression:

[tblSalePrice]-Sum(tblCost.tblCostAmount) AS Profit

 

[John Spencer]

You usually cannot refer to a field alias in a query and must redo the entire
calculation.

SELECT DISTINCTROW tblSale.tblSaleID
, tblSale.tblListingID
, tblSale.tblSalePrice
, tblSale.tblSalePaid
, Sum(tblCost.tblCostAmount) AS SumOftblCostAmount
, [tblSalePrice]-Sum(tblCost.tblCostAmount) AS Profit
FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID =
tblSale.tblListingID) INNER JOIN tblCost ON tblListing.tblListingID =
tblCost.tblListingID
GROUP BY tblSale.tblSaleID
, tblSale.tblListingID
, tblSale.tblSalePrice
, tblSale.tblSalePaid


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave the Wave]

You usually cannot refer to a field alias in a query and must redo the entire
calculation.

SELECT DISTINCTROW tblSale.tblSaleID
, tblSale.tblListingID
, tblSale.tblSalePrice
, tblSale.tblSalePaid
, Sum(tblCost.tblCostAmount) AS SumOftblCostAmount
, [tblSalePrice]-Sum(tblCost.tblCostAmount) AS Profit
FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID =
tblSale.tblListingID) INNER JOIN tblCost ON tblListing.tblListingID =
tblCost.tblListingID
GROUP BY tblSale.tblSaleID
, tblSale.tblListingID
, tblSale.tblSalePrice
, tblSale.tblSalePaid


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a summary query that works great. It sums the various costs of
a sale and gives me a total as deisred. I want to know the profit, so
I try to calculate it by Profit:=[SalePrice]-[SumOfCost].

When I run the query, Access treats the [SumOfCost] like a parameter.
I don't enter a value, just hit enter, and the query calculates the
correct profit answer. Why would access insist the calculated field is
a parameter that needs to be entered?

Here is the SQL of the query:
SELECT DISTINCTROW tblSale.tblSaleID, tblSale.tblListingID,
tblSale.tblSalePrice, tblSale.tblSalePaid, Sum(tblCost.tblCostAmount)
AS SumOftblCostAmount, [tblSalePrice]-[SumOftblCostAmount] AS Profit
FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID =
tblSale.tblListingID) INNER JOIN tblCost ON tblListing.tblListingID =
tblCost.tblListingID
GROUP BY tblSale.tblSaleID, tblSale.tblListingID,
tblSale.tblSalePrice, tblSale.tblSalePaid,
[tblSalePrice]-[SumOftblCostAmount];

ps. I used the "Build.." feature to insure the problem isn't a typo.

Thanks for your time and assistance!
David G.

Thank you for your comments.
In trying to implement your recommendations, I found that changing the
"Total" setting-in QBF view- from "Group By" to " Expression" took
care of the problem. My final SQL is:

SELECT DISTINCTROW tblSale.tblSaleID,
tblSale.tblSaleDate,
tblSale.tblSalePrice,
tblSale.tblListingID,
tblSale.tblSalePaid,
Sum(tblCost.tblCostAmount) AS SumOftblCostAmount,
[tblSalePrice]-[SumOftblCostAmount] AS Profit
FROM (tblListing INNER JOIN tblSale ON tblListing.tblListingID =
tblSale.tblListingID) INNER JOIN tblCost ON tblListing.tblListingID =
tblCost.tblListingID
GROUP BY tblSale.tblSaleID,
tblSale.tblSaleDate,
tblSale.tblSalePrice,
tblSale.tblListingID,
tblSale.tblSalePaid;

Changing the "Total" setting removed
[tblSalePrice]-[SumOftblCostAmount
from the GROUP BY portion of the SQL statement.