I have the following query which is almost what I need.SELECT porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal, SUM(items.Price) AS SumOfLabor, Null as SumOfMaterials, items.TypeFROM porders INNER JOIN items ON porders.PONum = items.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorID INNER JOIN postatus ON porders.StatusID = postatus.StatusIDWHERE (vendors.VendorName = @SearchValue) GROUP BY porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal, items.TypeHAVING (items.Type = 'L') AND (porders.Date BETWEEN @StartDate AND @StopDate)UNION ALLSELECT porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal, Null as SumOfLabor, SUM(items.Price) AS SumOfMaterials, items.TypeFROM porders INNER JOIN items ON porders.PONum = items.PONum INNER JOIN vendors ON porders.VendorID = vendors.VendorID INNER JOIN postatus ON porders.StatusID = postatus.StatusIDWHERE (vendors.VendorName = @SearchValue)GROUP BY porders.PONum, porders.VendorID, porders.MPONum, vendors.VendorName, porders.Date, postatus.StatusName, porders.Tax, porders.Freight, porders.POTotal, items.TypeHAVING (items.Type = 'M') AND (porders.Date BETWEEN @StartDate AND @StopDate)ORDER BY PONum
I have POrders-(Parent) and Items-(Child) tables. In the items table when they enter an ordered item they choose whether it is L(Labor) or M(Materials). I am trying to do a query where the user can enter a Vendor and Start & Stop Dates and then Sum the items.Price column for each Order. So it would have PO# and then SumOfLabor and SumOfMaterials. My query above will return the sum of those columns but it puts each as it's own record. Is there a way to have it fill in the SumOfMaterials if that PO already exists (from the SumOfLabor query) and if it doesn't then it would enter a new record for that PO? This is what it returns...PONum VendID MPONum VendorName Date Status Tax Freight POTotal SumOfLabor SumOfMaterials Type12307 386 M12307 Dahlke Trailer Sales 2013-01-07 Open NULL NULL 517.68 210 NULL L12307 386 M12307 Dahlke Trailer Sales 2013-01-07 Open NULL NULL 517.68 NULL 307.68000030517 MI want...PONum VendID MPONum VendorName Date Status Tax Freight POTotal SumOfLabor SumOfMaterials Type12307 386 M12307 Dahlke Trailer Sales 2013-01-07 Open NULL NULL 517.68 210 307.68000030517 MI don't need the Type field - was just using that for testing.Thanks for your help.Stacy