Sorting out of order 2

[Guest]

Hi All,

Under “Sorting and Grouping†I sorted my report as follows:
____________________________________
| |
| Field/Expression Sort Order |
| |
| Date Ascending |
| Door Ascending |
| Stop Ascending |
|____________________________________|

This is the sorting result:

Date Door Stop
02/01/06 321 1
02/03/06 130 2
02/03/06 202 1
02/07/06 127 1
02/09/06 221 1
02/20/06 130 1
02/25/06 325 1
03/05/06 250 1

I need it to sort like this:

Date Door Stop
02/01/06 321 1
02/03/06 202 1
02/07/06 127 1
02/09/06 221 1

02/20/06 130 1
02/03/06 130 2

02/25/06 325 1
03/05/06 250 1

Can this be done?

Thanks,

Cage

 

[Jeff Boyce]

I'm afraid I don't see the pattern in the "sort it this way" example. Can
you describe, in words, which field is sorted on first, in which order, then
the second field and order, and the third field and order?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

It sorts the Date first than the Door than the Stop, but if the door row has
the same door as another record than I need it to sort the date up to where
the duplicate door is, than it needs to combine the 2 doors together and than
continua with the date sorting.

Thanks,

Cage

 

[Jeff Boyce]

Cage

I guess I'm still having trouble following...

It sounds like you want it to sort one way, but then change the sorting if
something happens during the sorting.

If this is what you are after, one approach might be to create a procedure
that opens the recordset, steps through the rows one by one, fills them into
an array, applies your "rules" to re-order the array, and generates a final
sorting.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

Under “Sorting and Grouping” I sorted my report as follows:
____________________________________
| |
| Field/Expression Sort Order |
| |
| Date Ascending |
| Door Ascending |
| Stop Ascending |
|____________________________________|

This is the sorting result:

Date Door Stop
02/01/06 321 1
02/03/06 130 2
02/03/06 202 1
02/07/06 127 1
02/09/06 221 1
02/20/06 130 1
02/25/06 325 1
03/05/06 250 1

I need it to sort like this:

Date Door Stop
02/01/06 321 1
02/03/06 202 1
02/07/06 127 1
02/09/06 221 1

02/20/06 130 1
02/03/06 130 2

02/25/06 325 1
03/05/06 250 1

Since the sorting date of records with Stop >1 is the date
of the recorresponding record with Stop =1, there is no way
for the report to figure it out. Instead you need to modify
the report's record source query to calculate a sort date
field that the report can use:

SELECT [Date], Door, Stop,
(SELECT X.[Date]
FROM table As X
WHERE X.Door = table.Door
AND X.Stop = 1) As SortDate
FROM table

Then set the report's Sorting and grouping to:

SortDate Ascending
Door Ascending
Stop Ascending