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
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