Sort Order clean-up query (1 Viewer)

[AOB]

I have a number of tables which include a SortOrder column (i.e. to allow the contents to be sorted in a query based on some attribute other than simple alphabetical or numerical order of a particular field or combination of fields)

ID	Value	SortOrder
4	ABC	100
2	DEF	200
3	GHI	300
1	JKL	400

The SortOrder field is a simple numeric one - as a general rule, I use iterations of 100 to provide an ample "gap" in which to insert new records, for example :

ID	Value	SortOrder
4	ABC	100
2	DEF	200
6	PQR	250
3	GHI	300
5	MNO	350
1	JKL	400

I want to write a query that "cleans-up" this SortOrder when new records are added, to restore that standard gap of 100 between each record, for example :

ID	Value	SortOrder
4	ABC	100
2	DEF	200
6	PQR	300
3	GHI	400
5	MNO	500
1	JKL	600

In other words, create some sort of ranking query based on the existing SortOrder values, then update the SortOrder values based on that ranking

Here's my ranking query :

SELECT T1.ID, T1.SortOrder, (SELECT Count(*) FROM tbl AS T2 WHERE T2.SortOrder <= T1.SortOrder ) AS Rank
FROM tbl T1

But I can't perform the update with this as a sub-query...

UPDATE tbl T
INNER JOIN
(SELECT T1.ID, (SELECT Count(*) FROM tbl AS T2 WHERE T2.SortOrder <= T1.SortOrder ) AS Rank
FROM tbl T1) R ON T.ID = R.ID
SET T.SortOrder = R.Rank * 100

...as :

"Operation must use an updateable query"

Any suggestions as to how I might go about this? Preferably as efficiently as possible; the tables are relatively small but I'd like something nice and quick that I can just fire whenever a new record is added or an existing record deleted (or, an existing record is updated with a different SortOrder) to keep that field nice and neat?

Thanks

AOB

 

[Pat Hartman]

This sample db does that. Look at the child table example.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk

 

[ebs17]

to keep that field nice and neat?

Trivialities should not be exaggerated.

"Operation must use an updateable query"

Jet is just too goofy when using jointed subqueries in update queries.

Conceivable workarounds:
- Store subquery as saved query and use it in JOIN. Worse: store subquery as temporary table.
- Assigning the new content via domain aggregate function.
- Traverse sorted table by recordset and thus reset the SortOrder. This action should be the easiest, problem-free and fastest.

 

[Pat Hartman]

In the example, the BeforeInsert event calculates the next Item Number for new items. You can change it later if you want to. The Button is where the renumber code lives.

 

[MajP]

This might interest you

MS Access: custom control built in another language

Hi, I have a need for a specific control to be built for an Access application. I have built this control in a rather crude way with the existing built in controls using VBA. But performance is rather poor due to lots of looping. Some of the performance can be improved with some code...

www.access-programmers.co.uk

allows you to sort any list and persist the order

 

[arnelgp]

modify your SortOrder field and change it to Long Integer instead of string.
you can Open a Recordset and update it from there:

Public Function fnFixSortOrder()
    Const TABLE As String = "yourTable"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim initial As Long
    initial = 100
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT SortOrder FROM " & TABLE & " Order By SortOrder;", dbOpenDynaset)
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            .Edit
            !SortOrder = initial
            .Update
            initial = initial + 100
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Function

 

[AOB]

Excellent, thank you both Pat & arnelgp! Looks like you both have the same logic in play.

Far more straightforward than what I ended up doing as I was concerned that updating the SortOrder field by looping through the recordset, while I had the recordset open, could potentially result in a conflict (e.g. when moving from one record to the next, the "next" record could be impacted by the fact I had just changed the SortOrder of the previous record, and the "next" record might not be what I think it is!)

The way I was doing it, I was loading one recordset with the existing keys and "future" sort orders based on the rank query, then storing those keys and "future" sort orders in a dictionary, closing that recordset (can't perform the update with it as the join for the rank makes it non-updateable), open a new recordset of just the keys and "future" sort orders, loop through that and assign the new sort orders from the dictionary.

But having tested this method, it doesn't appear that any such conflict / record-skipping exists, so I have replaced my original workaround with a flavour of yours. Performance-wise, there's no perceptible difference (my tables are tiny), but I would imagine your method is far more scalable if they were to grow.

Thank you both again so much, really appreciate the pointers!

 

[Pat Hartman]

I agree, having a form open while you run an update query (as my sample does) or while running a VBA loop to update the recordset (as my sample does) can be dangerous. NOTICE that MY code saves the current record before doing anything. That isolates the form from the other processes. Remember. Suzy in Accounting can be updating the same data without issue. Access handles conflicts with ease. Where you have trouble is when you conflict with yourself Saving the current record before opening a form or report or running a query or doing pretty much anything else that could somehow affect the current record is the key. Don't even think about it. Make it a habit.

The other solution to doing a resequence that doesn't require the add 1000 query, is to sort the recordset into descending order and apply the sequence number from high to low rather than low to high.