SELECT tbl_orders.sales_order_id AS [order], tbl_companies.company_name, tbl_order_details.product_code, tbl_order_details.item_number AS item, tbl_order_details.quantity AS qty, [tbl_orders].[currency_rate]*[tbl_order_details].[price] AS price_sterling, Sum(Nz([quantity_delivered],'0')) AS Delivered, [quantity]-[Delivered]+[qty_returned] AS Outstanding, [outstanding]*([tbl_orders].[currency_rate]*[tbl_order_details].[price]) AS outstanding_value, (([tbl_orders].[currency_rate]*[tbl_order_details].[price])*[quantity]) AS total_order_value, ([outstanding_value]/[total_order_value]) AS percentage_remaining, tbl_item_types.item_type_name , tbl_secondary_item_type.secondary_item_type_name, tbl_primary_colours.primary_colour_name, tbl_orders.order_date, tbl_denominations.denomination_name, tbl_order_details.item_complete, tbl_orders.order_cancelled, tbl_order_details.detail_id, tbl_order_details.Despatch_Date, tbl_orders.proforma, tbl_orders.ccp_id, tbl_order_details.price, tbl_currencies.currency_name, t
bl_order_details.customer_item_reference, tbl_staff.staff_initials, tbl_orders.customers_ref, tbl_deliveries.entry_complete, Nz([SumOfquantity_returned],0) AS qty_returned FROM value_of_orders_all_returns RIGHT JOIN (((tbl_currencies INNER JOIN (tbl_denominations RIGHT JOIN (tbl_secondary_item_type INNER JOIN (tbl_primary_colours INNER JOIN (tbl_item_types INNER JOIN (((tbl_delivery_details RIGHT JOIN (tbl_order_details INNER JOIN tbl_orders ON tbl_order_details.sales_order_id = tbl_orders.sales_order_id) ON tbl_delivery_details.item_id = tbl_order_details.detail_id) INNER JOIN tbl_companies ON tbl_orders.company_id = tbl_companies.company_id) INNER JOIN tbl_products ON tbl_order_details.product_code = tbl_products.product_code) ON tbl_item_types.item_type_id = tbl_products.item_type_id) ON tbl_primary_colours.primary_colour_id = tbl_products.primary_colour_id) ON tbl_secondary_item_type.secondary_item_type_id = tbl_products.secondary_item_type_id) ON tbl_denominations.denomination_id = tbl_products.min_s
ales_quantity_denom_id) ON tbl_currencies.currency_id = tbl_orders.sales_order_currency_id) INNER JOIN tbl_staff ON tbl_orders.ccp_id = tbl_staff.staff_id) LEFT JOIN tbl_deliveries ON tbl_delivery_details.delivery_note_id = tbl_deliveries.delivery_note_id) ON value_of_orders_all_returns.sales_order_item_id = tbl_order_details.detail_id GROUP BY tbl_orders.sales_order_id, tbl_companies.company_name, tbl_order_details.product_code, tbl_order_details.item_number, tbl_order_details.quantity, [tbl_orders].[currency_rate]*[tbl_order_details].[price], tbl_item_types.item_type_name, tbl_secondary_item_type.secondary_item_type_name, tbl_primary_colours.primary_colour_name, tbl_orders.order_date, tbl_denominations.denomination_name, tbl_order_details.item_complete, tbl_orders.order_cancelled, tbl_order_details.detail_id, tbl_order_details.despatch_date, tbl_orders.proforma, tbl_orders.ccp_id, tbl_order_details.price, tbl_currencies.currency_name, tbl_order_details.customer_item_reference , tbl_staff.staff_initials
, tbl_orders.customers_ref, tbl_deliveries.entry_complete, Nz([SumOfquantity_returned], 0), tbl_orders.currency_rate, tbl_order_details.price, tbl_orders.ccp_id, tbl_orders.order_complete, tbl_orders.order_entry_complete HAVING (((tbl_order_details.item_complete) = 0) And ((tbl_orders.order_cancelled) = 0) And ((tbl_deliveries.entry_complete) Is Null Or (tbl_deliveries.entry_complete) = -1) And ((tbl_orders.order_complete) = 0)) AND (tbl_orders.ccp_id = 5) ORDER BY tbl_orders.sales_order_id, tbl_order_details.item_number;