Hi there

I can't work out whats wrong with this query, it comes with Acces 2003 as part of the accounts database sample.

Basically if there are no parts with a work order, therefore No [Parts Totals by Workorder].[Parts Total] then Labor Total is not selected, meaning the [Order Total] incorrectly states zero.

I'm really struggling and I hope someone out there enjoys a challenge. :)

Thanks

Alice


SELECT DISTINCTROW Customers.CompanyName, [ContactLastName] & ", " & [ContactFirstName]
AS [Contact Name], Customers.PhoneNumber, Workorders.WorkorderID, Workorders.DateFinished, Workorders.DateRequired, Workorders.MakeAndModel, (CLng((nz([Labor Totals by Workorder].[Labor Total])+nz([Parts Totals by Workorder].[Parts Total]))*[SalesTaxRate]*100)/100)+[Labor Total]+[Parts Total]
AS [Order Total]
FROM Customers INNER JOIN ((Workorders LEFT JOIN [Labor Totals by Workorder] ON Workorders.WorkorderID = [Labor Totals by Workorder].WorkorderID) LEFT JOIN [Parts Totals by Workorder] ON Workorders.WorkorderID = [Parts Totals by Workorder].WorkorderID) ON Customers.CustomerID = Workorders.CustomerID
WHERE (((Workorders.DateFinished) Is Not Null) AND ((Workorders.DatePickedUp) Is Null));