Query only shows record if there are matching items in a linked table

[dipique]

Table 1: PurchaseOrders
This table contains simple meta-data about a PO.

Table 2: OrderedItems
Contains items that are on the purchase orders. Linked by PO Number (primary key for the PurchaseOrders table). Includes details like tax, shipping, cost, etc.

Table 3: A lookup table that contains vendors
This table contains simple meta-data about a PO.

Query 1: qryOrderWithVendors
This Query is based on Purchase Orders and brings in the sum of the tax, shipping, etc. dollar amounts so I can say, "The total for PO 123 is $84.19" without needing to do any calculations.



My Problem
When I run the query, it only brings in purchase orders that have associated items that have been sold. It does not display anything that doesn't have any items yet. I would like it to show them as $0.

The Code

SELECT DISTINCT PurchaseOrders.ID, PurchaseOrders.PODT, PurchaseOrders.DateOrdered, PurchaseOrders.OrderedBy, Vendors.VendorName, Sum(OrderedItems.[UnitCost]*OrderedItems.Quantity) AS NetCost, Sum([OrderedItems].[UnitShipping]*[OrderedItems].[Quantity]) AS Shipping, Sum([OrderedItems].[UnitTax]*[OrderedItems].[Quantity]) AS Tax, Sum(OrderedItems.LineCost) AS OrderCost
FROM Vendors INNER JOIN (PurchaseOrders INNER JOIN OrderedItems ON PurchaseOrders.ID = OrderedItems.PONumber) ON Vendors.ID = PurchaseOrders.Vendor
GROUP BY PurchaseOrders.ID, PurchaseOrders.PODT, PurchaseOrders.DateOrdered, PurchaseOrders.OrderedBy, Vendors.VendorName;

Please help!

Thank you,

Dan

[pbaldy]

In design view of the query, right-click on the line joining fields together to edit the join. You'll see your available options. In SQL view, that's the equivalent of changing INNER to RIGHT or LEFT as appropriate.

[dipique]

You, sir are a miracle worker. Here's the new SQL that, of course, works like a dream:

SELECT DISTINCT PurchaseOrders.ID, PurchaseOrders.PODT, PurchaseOrders.DateOrdered, PurchaseOrders.OrderedBy, Vendors.VendorName, Sum(OrderedItems.[UnitCost]*OrderedItems.Quantity) AS NetCost, Sum([OrderedItems].[UnitShipping]*[OrderedItems].[Quantity]) AS Shipping, Sum([OrderedItems].[UnitTax]*[OrderedItems].[Quantity]) AS Tax, Sum(OrderedItems.LineCost) AS OrderCost
FROM Vendors INNER JOIN (PurchaseOrders LEFT JOIN OrderedItems ON PurchaseOrders.ID = OrderedItems.PONumber) ON Vendors.ID = PurchaseOrders.Vendor
GROUP BY PurchaseOrders.ID, PurchaseOrders.PODT, PurchaseOrders.DateOrdered, PurchaseOrders.OrderedBy, Vendors.VendorName;

Thanks!

Dan

[pbaldy]

Happy to help!