Return Order Value (IIF?) (1 Viewer)

[scouser]

Hi to all. The code below returns a zero if no payment has been made. I would like it to return the value for Sum([tblSalesOrderDetails].[LineTotal]) (i.e. whatever the order value is).

OrderBalance: Nz(Sum([tblSalesOrderDetails].[LineTotal])-[tblPayments].[Total],0)

Query code - SQL

SELECT qrySalesOrders.OrderID, qrySalesOrders.CustomerName, qrySalesOrders.OrderDate, qrySalesOrders.ConfirmedStudioDate, qrySalesOrders.OrderStatus, Sum(Nz([LineTotal],0)) AS OrderValue, tblPayments.Total AS Payments, Nz(Sum([tblSalesOrderDetails].[LineTotal])-[tblPayments].[Total],0) AS OrderBalance
FROM tblPayments RIGHT JOIN (tblSalesOrderDetails INNER JOIN qrySalesOrders ON tblSalesOrderDetails.OrderID = qrySalesOrders.OrderID) ON tblPayments.OrderId = qrySalesOrders.OrderID
GROUP BY qrySalesOrders.OrderID, qrySalesOrders.CustomerName, qrySalesOrders.OrderDate, qrySalesOrders.ConfirmedStudioDate, qrySalesOrders.OrderStatus, tblPayments.Total
HAVING (((qrySalesOrders.OrderStatus)=False))
ORDER BY qrySalesOrders.OrderDate;

Thans in advance,
Phil.

 

[plog]

You're working at 2 different levels there:

OrderBalance: Nz(Sum([tblSalesOrderDetails].[LineTotal])-[tblPayments].[Total],0)

You can't work at the aggregate (SUM of LineTotal) and the individual record level (tblPayments) at the same time. I don't know what you are trying to accomplish, but this may require a subquery to get the total (SUM) first and then do the math.

Further, you probably shouldn't have credits and debits in seperate tables.

 

[Minty]

I Think you only need

OrderBalance: Nz(Sum([tblSalesOrderDetails].[LineTotal])-[tblPayments].[Total],Sum([tblSalesOrderDetails].[LineTotal]))

 

[scouser]

Thanks Minty, spot on.
Phil.

 

[arnelgp]

OrderBalance: Nz(Sum([tblSalesOrderDetails].[LineTotal])-[tblPayments].[Total],Sum([tblSalesOrderDetails].[LineTotal]))