I have a relatively complex query that shows delivered orders and calculates the number of days they were late.
I want to sort the results of the query by the field days_late which is [delivery_date]-[despatch_date] but as you can see by the screenshot below, It partially sorts the results but then somehow splits them and then sorts the second part of the results.
I am only sorting on the one field and there is no additional sorting applied in the query's properties
Screenshot:
http://snag.gy/FhmYW.jpg
SQL code below:
Any suggestions?
I want to sort the results of the query by the field days_late which is [delivery_date]-[despatch_date] but as you can see by the screenshot below, It partially sorts the results but then somehow splits them and then sorts the second part of the results.
I am only sorting on the one field and there is no additional sorting applied in the query's properties
Screenshot:
http://snag.gy/FhmYW.jpg
SQL code below:
Code:
SELECT tbl_orders.sales_order_id, tbl_order_details.item_number, tbl_order_details.product_code, tbl_order_details.quantity, tbl_order_details.price, tbl_order_details.despatch_date, tbl_deliveries.delivery_note_id, tbl_deliveries.delivery_date, tbl_delivery_details.quantity_delivered, [delivery_date]-[despatch_date] AS days_late, tbl_contracts.contract_reference, tbl_products.nsn, tbl_item_types.item_type_name, tbl_secondary_item_type.secondary_item_type_name, tbl_products.design, tbl_products.sort_colour
FROM ((((tbl_deliveries INNER JOIN tbl_delivery_details ON tbl_deliveries.delivery_note_id = tbl_delivery_details.delivery_note_id) INNER JOIN ((tbl_orders INNER JOIN tbl_order_details ON tbl_orders.sales_order_id = tbl_order_details.sales_order_id) INNER JOIN tbl_contracts ON tbl_orders.contract_id = tbl_contracts.contract_id) ON tbl_delivery_details.item_id = tbl_order_details.detail_id) INNER JOIN tbl_products ON tbl_order_details.product_code = tbl_products.product_code) INNER JOIN tbl_secondary_item_type ON tbl_products.secondary_item_type_id = tbl_secondary_item_type.secondary_item_type_id) INNER JOIN tbl_item_types ON tbl_products.item_type_id = tbl_item_types.item_type_id
WHERE (((tbl_deliveries.delivery_date)>[tbl_order_details].[despatch_date]) AND ((tbl_orders.contract_id)=[Forms].[ceremonial_contract_kpi_information].[cbo_contract_id]) AND ((tbl_orders.order_date) Between [Forms].[ceremonial_contract_kpi_information].[txt_year1_start_date] And [Forms].[ceremonial_contract_kpi_information].[txt_year1_end_date]))
ORDER BY [delivery_date]-[despatch_date];
Any suggestions?