Microsoft Access: Insert order or ranking number based on value of a field

Hi, I have the following result from a query.  I'd like to create the last column to redo the order based on field "Order" ignoring the missing orders.  Thanks.

Field1   Field2   Order   Revised_Order
                                        (Need help to create this column)
XXX       YYY       1           1
XXX       YYY       2           2
XXX       YYY       5           3
XXX       YYY       7           4
AAA      BBB      3           1
AAA      BBB      6           2
AAA      BBB      8           3
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I am not sure how you want to define the 'Revised_Order' column.
If there is a rule how to compute the value you want to enter in there, it may either be computed and added to the first query as a computed field, or you create a query on top of the first one that will compute and add this.

Inside the SQL query you can e.g. add
 ( [mytable].[myfield] * value ) AS MYOUTPUTFIELD

The phrase inside my () is whatever calculation, or eventually referenz you might have and it is named by "AS MYOUTPUTFIELD" to "MYOUTPUTFIELD"

So in your case Revised_Order
You can do this not only in the SQL view but also inside the QBE view, just by writing this expression into the "Field" line as a new field like
MYOUTPUTFIELD: (mytable.myfield * value)

This expression defines the same.
If you need an example you have to describe more on the rules you have to decide what is entered into the Revised_Order field.
JCJGAuthor Commented:
Can you suggest a way to compute?  I need it to start from one with an increment of one at each increase of the "
JCJGAuthor Commented:
"Order"  field regardless of how much of the increase.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.


there may be "nicer" ways, but I did it now this way:
Use "Create Modul" and add this function:
Public Static Function countIndex(inVal) As Integer
    Dim keep As Integer
    If (inVal <= keep) Then
        keep = 1
        keep = keep + 1
    End If
    countIndex = keep
End Function

Open in new window

Then Create a Query containing this SQL:
SELECT Orders.Field1, Orders.Field2, Orders.Order, ( countIndex(Orders.[Order] ) ) AS Revised_Order
FROM Orders
GROUP BY Orders.Field1, Orders.Field2, Orders.Order
ORDER BY Orders.Field1, Orders.Field2;

Open in new window

As this is a calculated field, remember that the counting may change depending on order of your datasets. If this must be stored in a database, you do this better as an actualization query that inserts those created datasets at a given time and will not touch them at any time later.

The function is doing exactly as you said, as long as the incoming value (when sorted and grouped as given) be smaller than the memorized, it resets to 1 otherwise it adds +1

Hope this is what you wanted.

Below my output:
Query Result
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Assuming your table is called 'Table1':

SELECT Table1_Alias.Field1, Table1_Alias.Field2, Table1_Alias.Order, (Select Count(*) from Table1 Where [Order] < [Table1_Alias].[Order]+1 and [Field1]=[table1_alias].[Field1] and [Field2]=[table1_alias].[Field2];) AS RevisedOrder
FROM Table1 AS Table1_Alias;

JCJGAuthor Commented:
Thanks for your feedback.

Printnix63: For some reasons, my query result doesn't always show the first record as 1.  When I click on some of the cells the number changes overlapping the original number.  Very strange.

Jim - It looks like the order rankings are correct.  The only issue is it takes a long time to run and when I click on the query result, Access crashes.  I was running the query on a query, not a table.  There are about 200,000 records.

Can you make further suggestions?  Thanks!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Can you make further suggestions?  Thanks! >>

 Make sure you set the UseTransaction property of the query to no.

 But it will be slow.  This is a query with a sub-select in it, so your running a query for each and every row.

 The faster method in this case would be to use VBA.  Open a recordset with the records ordered by field1 and 2.   Each time they change, reset a count variable to 1 and update the records as you move through the recordset.  Should take seconds.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Basically, changing my script to use the proposal of Jim, it would either be with below query:
SELECT Orders.Field1, Orders.Field2, Orders.Order, (createIndex(Orders.Field1,Orders.Field2)) AS Revised_Order
FROM Orders
ORDER BY Orders.Field1, Orders.Field2;

Open in new window

and below simple code, so you have a normal Query you can open.
Public Static Function createIndex(f1, f2) As Integer
    Dim k1
    Dim k2
    Dim ind As Integer
    ind = ind + 1
    If (k1 <> f1) Then
        k1 = f1
        ind = 1
    End If
    If (k2 <> f2) Then
        k2 = f2
        ind = 1
    End If
    createIndex = ind
End Function

Open in new window

The other possibility is to have a button on some Form that calls below procedure which will create this query on the fly:
Option Compare Database
Public Sub CreateRevisedOrder()
    On Error GoTo CatchErr
    On Error Resume Next
    Dim db As Database
    Dim qdef As QueryDef
    Dim SQL As String
    SQL = "SELECT Orders.Field1, Orders.Field2, Orders.[Order], ( createIndex(Orders.Field1,Orders.Field2 ) ) AS Revised_Order " _
          & "FROM Orders " _
          & "ORDER BY Orders.Field1 ASC, Orders.Field2 ASC"
    Set db = CurrentDb
    db.QueryDefs.Delete ("Revised Orders")
    Set qdef = db.CreateQueryDef("Revised Orders", SQL)
    Select Case Err.Number
        Case 0
            'everything is fine
            DoCmd.OpenQuery "Revised Orders", acViewNormal, acReadOnly ' if it must be edited use acEdit
        Case 3265 ' Query does not exist, so what...
        Case 3012 ' Query does already exist ... should be ok as well
        Case Else
            MsgBox Err.Description & ": " & Err.Number
    End Select
End Sub

Open in new window

I have as well checked on what you wrote, when entering the field, this must be something inside Access, if you press F5 it shows all records as they should. What is even more irritating, was, that I open the query as acReadOnly, and it still changes the displayed values while you are in the field. (F5 refreshes and puts the correct values back in)
JCJGAuthor Commented:
Thanks again.  Unfortunately I can't get the codes to work.  The query returns some strange order numbers which are corrected briefly after pressing F5.  The numbers revert back after I search for a particular record.  My plan is to create a crosstab query on this query and have the order ranking as the columns.  That's why I'd like to standardize them based on the revised ranking.  Obviously the crosstab query didn't produce accurate result because of the refreshing issue above.  Is there another way to do this?
Is the previous Order always  unique values? There are no repeated orders, and no blank values?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The query returns some strange order numbers which are corrected briefly after pressing F5.  The numbers revert back after I search for a particular record. >>

 Your getting this because that's the way records are populated in a result set.   Assigning a value in with a procedure only works if you make one pass through a result set.  For example, if you did an append or make table query.

Maybe I mis-understood the problem, but if this is something you want "on the fly" and not as a one time update, then your going to need to store the value in some way.  Either:

1. By adding a field to the base table
2. Creating a temp table with the PK of the main record and a field to hold the order value.

 There are some variations on that of course (like storing the entire record in the temp table), but one way or another, your going to need to hold the revised_order value.

Try this ample database
Run the make table query to use the table  in making a crosstab.
Used table b0(aid, bid, n)
Used vba.
JCJGAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for JCJG's comment #a39586805
Assisted answer: 250 points for JDettman's comment #a39586451
Assisted answer: 250 points for printnix63's comment #a39586578

for the following reason:

I appreciate your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.