Query Prompting for data when it has all arguments

[RJGoodhead]

I've created a query using design view in Access 2011. Part of it has 2 expressions (Sales and Cost) that i then need to find the difference of. I have done this with a third expression, GrossProfit.

For some reason when running the query Access prompts for the value of Sales and Cost, but in the final datasheet the values are correctly calculated

Any thoughts please?

SQL below, have separated and highlighted the 3 expressions

SELECT OrdersTable.InvoiceDate, OrdersTable.InvoiceNumber, OrdersTable.OrderNumber, OrdersTable.CustomerName,
OrdersTable.CostCentre, SerialTable.Model, SerialTable.SerialNumber, 
 
[COLOR=red]Nz([OrdersTable].[TotalInvoiceValue])*[OrdersTable].[ExchangeRate] AS Sales,[/COLOR]
 
[COLOR=red]Nz(Sum([AtchdInvoicesTable].[DollarValue])+(Nz([OrdersTable].[CustomerProvision])+[/COLOR]
[COLOR=red]Nz([OrdersTable].[SpaProvision]))*[OrdersTable].[ExchangeRate]) AS Cost,[/COLOR]
 
[COLOR=red][Sales]-[Cost] AS GrossProfit,[/COLOR]
 
 OrdersTable.TerritoryCode, OrdersTable.CustomerProvision, OrdersTable.SpaProvision, OrdersTable.ExchangeRate
 
FROM (OrdersTable INNER JOIN AtchdInvoicesTable ON OrdersTable.OrderNumber = AtchdInvoicesTable.OrderNumber)
 INNER JOIN SerialTable ON OrdersTable.OrderNumber = SerialTable.OrderNumber
 
GROUP BY OrdersTable.InvoiceDate, OrdersTable.InvoiceNumber, OrdersTable.OrderNumber, 
OrdersTable.CustomerName, OrdersTable.CostCentre, SerialTable.Model, SerialTable.SerialNumber,
Nz([OrdersTable].[TotalInvoiceValue])*[OrdersTable].[ExchangeRate], [Sales]-[Cost], 
OrdersTable.TerritoryCode, OrdersTable.CustomerProvision, OrdersTable.SpaProvision,
 OrdersTable.ExchangeRate;

 

[RJGoodhead]

Fixed,

For information: the reason it was prompting was that the "Total:" option for "GrossProfit" needed to be set as an expression to match the aggregate (features 'sum') "Cost" function.

It came to me as I was formatting the SQL code to post.

Thank you for your time if you came to this thread to help

 

[tehNellie]

Glad you got it working and thanks for sharing what you did to fix the issue.