scouser
Registered User.
- Local time
- Today, 14:01
- Joined
- Nov 25, 2003
- Messages
- 767
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).
Query code - SQL
Thans in advance,
Phil.
OrderBalance: Nz(Sum([tblSalesOrderDetails].[LineTotal])-[tblPayments].[Total],0)
Query code - SQL
Code:
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;
Phil.