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

Hi,

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.

Can you suggest a way to compute?  I need it to start from one with an increment of one at each increase of the "

"Order"  field regardless of how much of the increase.

Hi,

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
    Else
        keep = keep + 1
    End If
    countIndex = keep
End Function

Select allOpen 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;

Select allOpen 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:

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;

Jim.

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!

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?

<<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.

Jim.

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.
order.accdb

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!