Big query I can't get my head around..

[Artimus]

Here is what I have so far;

SELECT ServiceJobCards.Job_Number, ServiceJobCards.Work_Done_Date, ServiceJobCards.DeliveryType, ServiceJobCards.ServiceType, ServiceJobCards.CustomerID, ServiceJobCards.ModelNumber, Customers.[First Name], Customers.Surname, Sum([ServicePartsOrders]![Unit Price]*[ServicePartsOrders]![Quantity]) AS Expr1, ServiceJobCards.Labour
FROM (ServiceJobCards INNER JOIN Customers ON ServiceJobCards.CustomerID = Customers.CustomerID) INNER JOIN ServicePartsOrders ON ServiceJobCards.Job_Number = ServicePartsOrders.CustomerOrderID
WHERE (((ServiceJobCards.Status)=1) AND ((ServiceJobCards.Paid)=2))
GROUP BY ServiceJobCards.Job_Number, ServiceJobCards.Work_Done_Date, ServiceJobCards.DeliveryType, ServiceJobCards.ServiceType, ServiceJobCards.CustomerID, ServiceJobCards.ModelNumber, Customers.[First Name], Customers.Surname, ServiceJobCards.Labour;

Which does exactly what I want, except for records in ServiceJobCards where Sum([ServicePartsOrders]![Unit Price]*[ServicePartsOrders]![Quantity]) is equal to 0. (Ie, they didn't order any parts).

Those records don't show up.

Any ideas?

 

[jdraw]

Inner Join relates records where values are equal. You may want to investigate different Join options.
Right join http://www.w3schools.com/sql/sql_join_right.asp
Left Join http://www.w3schools.com/sql/sql_join_left.asp