Do this with no subquery??

[imstillatwork]

Can I get a recordset like this with no subquery? My host (crystaltech is not on 4.1 and I'm getting out of MS Access)

	SELECT 
		OI.Item_ID
		,OI.Order_Item_Name
		,OI.Order_Item_Quantity
		,Order_Item_Number
		,Order_Item_Price
		,(SELECT SUM(OI1.Order_Item_Quantity) FROM Order_Items AS OI1 WHERE OI1.Order_Item_number = OI.Order_Item_number) AS qty
	FROM 
		Order_Items AS OI
		LEFT JOIN Order_Master AS OM
		ON OM.Order_Invoice = OI.Order_Invoice
		
	ORDER BY 	
		Order_Item_Name

 

[nego78]

i didn't test it but it should work

 SELECT
        OI.Item_ID
        ,OI.Order_Item_Name
        ,OI.Order_Item_Quantity
        ,Order_Item_Number
        ,Order_Item_Price
        ,SUM(OI1.Order_Item_Quantity) AS Qty
    FROM
        Order_Items AS OI
        LEFT JOIN Order_Master AS OM
        ON OM.Order_Invoice = OI.Order_Invoice
        LEFT JOIN Order_Items AS OI1 WHERE OI1.Order_Item_number = OI.Order_Item_number
    GROUP BY OI.Item_ID
    ORDER BY
        Order_Item_Name

gry online

 

[imstillatwork]

Ahhh. I'l play with that as soon as I can... interesting.

Thanks!!

 

[imstillatwork]

with a few corrections and the new table / field names it is working great. thats for the jump start!

	 SELECT
        op.Order_product_name
        ,op.Order_product_qty
        ,op.Order_product_number
        ,op.Order_product_price
        ,SUM(op1.order_product_qty) AS sumqty
    FROM
        (nwcs_order_products AS op
		
     	LEFT JOIN nwcs_orders AS o
        ON o.order_id = op.order_id)
        
		LEFT JOIN nwcs_order_products AS op1 
		ON op1.order_product_id = op.order_product_id
    GROUP BY op.order_product_number
    ORDER BY
        sumqty