Sorting on a calculated field producing unexpected results (1 Viewer)

HairyArse

Registered User.
Local time
Today, 15:34
Joined
Mar 31, 2005
Messages
92
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:

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?
 

obeylele

Registered User.
Local time
Today, 19:34
Joined
Jul 29, 2005
Messages
28
First save the query as something like 'main_query' without where clause then give criteria to main query. You will have days_late in main query which you can use to order by.
 

HairyArse

Registered User.
Local time
Today, 15:34
Joined
Mar 31, 2005
Messages
92
If I run the query (as a new query) without any WHERE clauses, if I then do a manual sort on the days_late column, it still does exactly the same thing. The first 4868 records in my result set are sorted in order, but the remaining 600+ are then sorted again separately. The next hundred or so are then sorted, then the next twenty or so, followed by the next ten and then every result thereafter.

Seems very unusual and I can't figure out what's going wrong. I've recompiled, re-indexed, repaired and checked for data errors and none of this makes any difference.
 

HairyArse

Registered User.
Local time
Today, 15:34
Joined
Mar 31, 2005
Messages
92
That seems to have done the trip. Thanks a lot obeylele. :)
 

Users who are viewing this thread

Top Bottom