Renumbering

[Snowflake68]

Hi I have a table which has over 50 fields and I need to update the Operation number field with a new number putting them in reverse order.

There are over 2000 unique codes all of which have multiple records with various different information. (This is how is it set up in the ERP system so I am not able to change this)

So for example code 102655 has 28 records with Operation numbers 101 to 306 but I need to reverse the order and number them 1 to 28.

I know that I can append them into a new table in reverse sort order but how to I renumber the Operation number. I was thinking of somehow obtaining the record number as the new operation number but that will only work if I run the append query separately for each individual code but there are over 2000 of them so this is not really an option.

I was hoping that perhaps there was some VBA function that could be written that takes each code individually and then runs the append query updating the Operation number as it goes.

I have attached an example of one of the codes showing the current operation number and the new operation number that I what to obtain.

Hope someone can help.

 

[jdraw]

Please tell us the business rationale for this in simple, plain English. We need details if we are to offer more focused advice.
Tell us about the ERP system - what is is, what it contains.....etc.

The order of records in a table is never assured. If order is important, you use a query to select and order records as appropriate.

Relational tables have a unique identifier to identify/isolate one record from all others in the table.

I wouldn't change anything until I fully understood what was required, and reviewed options for how it might be done. 50 fields in a table may suggest a design/structure issue!!
Whatever you decide-- do it from a point of knowledge, not as a quick fix to a symptom.

Good luck with your project.

 

[Ranman256]

1st back it up.
2nd make a 'backup' field to hold the origninal #...[orig#]

then make a query that sorts everthing

, [operation] desc
Place this code into a module (or form) then run the query thru this code to renumber
usage:

"Code" is the example code field: 102655 
"OperationNum" is the # field to change

Renumber "qsMyQuery","Code","OperationNum"


[code]
Public Sub ReNumber(ByVal pvQry, pvDupeFld, ByVal pvChgFld)
'pvQry = query name
'pvDupeFld   = field with duplicate Group values
'pvChgFld    = field to change when duplicate is found
Dim vMsg
Dim db As Database
Dim rst   'As Recordset
Dim qdf As QueryDef
Dim vCurrDup, vPrevDup, vKey, vCurrFld, vAddr
Dim iNum As Long

DoCmd.SetWarnings False
        
Set db = CurrentDb
Set qdf = db.QueryDefs(pvQry)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
vPrevDup = "*&%"

With rst
    While Not .EOF
        'vAddr = .Fields("Address1")
        vCurrDup = .Fields(pvDupeFld) & ""
        
        If vCurrDup <> vPrevDup Then iNum = 1
               
                '-----------------------
                'renumber
                '-----------------------
            .Edit
            .Fields(pvChgFld) = iNum
            .Update
        
        iNum = iNum + 1
        vPrevDup = vCurrDup
        
       .MoveNext
    Wend
End With

Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrRemove:
MsgBox Err.Description, , mkCLASSNAME & "::RemoveDuplicates():" & Err
End Sub

 

[arnelgp]

here is a VBA function that will be
of help to you.

first, make sure to make a copy of your
table, just in case.

Create an Update query and include the
function on the Query:

Update table1 Set [Operation] = fncNewOP(

, [Operation]);

Copy and paste this code in Standard Module in VBA:
[code]
Option Compare Database
Option Explicit

Public Function fncNewOP(ByVal Code As Variant, ByVal Operation As Variant) As Variant

    Const CONST_TABLE As String = "TableX"  '<== replace this with your table name
    
    
    Dim rs As DAO.Recordset
    Dim recCount As Long
    Dim Delimiter As String
    
    Delimiter = ""
    If VarType(Code) = vbString Then
        Delimiter = Chr(34)
    End If
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT [Operation] FROM " & CONST_TABLE & _
        " WHERE [Code] = " & Delimiter & Code & Delimiter & " Order By [Operation]", dbOpenSnapshot)
        
    With rs
        If Not (.BOF And .EOF) Then
            .MoveLast
            recCount = .RecordCount
            .MoveFirst
        End If
        Do While Not .EOF
            If !Operation = Operation Then Exit Do
            recCount = recCount - 1
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    fncNewOP = recCount
End Function

also note the CONST_TABLE, replace TableX with the name
of your table being updated.

 

[Snowflake68]

Please tell us the business rationale for this in simple, plain English. We need details if we are to offer more focused advice.
Tell us about the ERP system - what is is, what it contains.....etc.

The order of records in a table is never assured. If order is important, you use a query to select and order records as appropriate.

Relational tables have a unique identifier to identify/isolate one record from all others in the table.

I wouldn't change anything until I fully understood what was required, and reviewed options for how it might be done. 50 fields in a table may suggest a design/structure issue!!
Whatever you decide-- do it from a point of knowledge, not as a quick fix to a symptom.

Good luck with your project.

The ERP system doesnt really come into question whilst I am doing this update as I am not updating the table itself as I have a copy of the data in local Access table. Once I have updated the Operations number field with the correct number then I will be replacing the whole table and at first this will be done on a test copy of the live system so no worries there.

The table has a unique ID which is just the row number but I will be using the existing Code in Ascending order plus the current operations code in descending order to specify the new order so this too is not a problem.

The problem I have is finding a way of generating the new number. It looks as though Ranman256 and arnelgp have provided possible solutions so I will give them a try. Thanks for your advice though, much appreciated.

 

[Snowflake68]

here is a VBA function that will be
of help to you.

first, make sure to make a copy of your
table, just in case.

Create an Update query and include the
function on the Query:

Update table1 Set [Operation] = fncNewOP(

, [Operation]);

Copy and paste this code in Standard Module in VBA:
[code]
Option Compare Database
Option Explicit

Public Function fncNewOP(ByVal Code As Variant, ByVal Operation As Variant) As Variant

    Const CONST_TABLE As String = "TableX"  '<== replace this with your table name
    
    
    Dim rs As DAO.Recordset
    Dim recCount As Long
    Dim Delimiter As String
    
    Delimiter = ""
    If VarType(Code) = vbString Then
        Delimiter = Chr(34)
    End If
    Set rs = CurrentDb.OpenRecordset( _
        "SELECT [Operation] FROM " & CONST_TABLE & _
        " WHERE [Code] = " & Delimiter & Code & Delimiter & " Order By [Operation]", dbOpenSnapshot)
        
    With rs
        If Not (.BOF And .EOF) Then
            .MoveLast
            recCount = .RecordCount
            .MoveFirst
        End If
        Do While Not .EOF
            If !Operation = Operation Then Exit Do
            recCount = recCount - 1
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    fncNewOP = recCount
End Function

also note the CONST_TABLE, replace TableX with the name
of your table being updated.

Thanks for this I will try later tomorrow as in meetings all day today now

 

[Snowflake68]

1st back it up.
2nd make a 'backup' field to hold the origninal #...[orig#]

then make a query that sorts everthing

, [operation] desc
Place this code into a module (or form) then run the query thru this code to renumber
usage:

"Code" is the example code field: 102655 
"OperationNum" is the # field to change

Renumber "qsMyQuery","Code","OperationNum"


[code]
Public Sub ReNumber(ByVal pvQry, pvDupeFld, ByVal pvChgFld)
'pvQry = query name
'pvDupeFld   = field with duplicate Group values
'pvChgFld    = field to change when duplicate is found
Dim vMsg
Dim db As Database
Dim rst   'As Recordset
Dim qdf As QueryDef
Dim vCurrDup, vPrevDup, vKey, vCurrFld, vAddr
Dim iNum As Long

DoCmd.SetWarnings False
        
Set db = CurrentDb
Set qdf = db.QueryDefs(pvQry)
Set rst = qdf.OpenRecordset(dbOpenDynaset)
vPrevDup = "*&%"

With rst
    While Not .EOF
        'vAddr = .Fields("Address1")
        vCurrDup = .Fields(pvDupeFld) & ""
        
        If vCurrDup <> vPrevDup Then iNum = 1
               
                '-----------------------
                'renumber
                '-----------------------
            .Edit
            .Fields(pvChgFld) = iNum
            .Update
        
        iNum = iNum + 1
        vPrevDup = vCurrDup
        
       .MoveNext
    Wend
End With

Set qdf = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub
ErrRemove:
MsgBox Err.Description, , mkCLASSNAME & "::RemoveDuplicates():" & Err
End Sub

Thanks for this I will try later tomorrow as in meetings all day today now

 

[The_Doc_Man]

This kind of is action is certainly possible - but you need to step away from the curb and learn how to not make so much work for yourself. jdraw mentioned this to you but that apparently got passed over. I wish to support his position AND amplify a little bit. Let me say it again: Re-ordering a table is a position of making more work for yourself than you really need.

With Access, you can NEVER presume the order of records in a table. EVER. You can rewrite the table in this order, close it, reopen it, and find that it isn't necessarily in the order you might have assumed. I'm not saying that would happen every time, but it CAN happen, particularly if there is a prime key in the table and your re-ordering is based on a non-prime key.

Further, Access never NEEDS to open a table directly, and when ordering is important, never SHOULD open the table directly. This is why you use queries. Queries are the work-horses of Access data control.

Access doesn't rely on tables for most things anyway - it relies on recordsets. You get those when you open a table OR a SELECT query. And 99+% of the time, Access does not care which one you opened.

You want things in a specific order? Write a query that includes an ORDER BY clause! If you want to see things in descending order, include the keyword modifier DESC after the field name, i.e. ORDER BY X DESC and use that query rather than rewriting the whole table. If you have to order on multiple fields, put the primary sort first, i.e. ORDER BY A, B DESC, C and voila, a triple sort with one in descending order. Further, even if one of those is the prime key, what you get doesn't depend on having such a key (unless, of course, A was the prime key).

Doing it this way is:

(a) quicker to design - one query vs. complex code involving a loop (or the other way, an INSERT INTO (Make Table) query that would have had to use an ORDER BY clause anyway.)

(b) faster to implement - a couple of lines of SQL vs. how many lines of VBA? (And part of implementation is TESTING, so include "faster to test" as part of this one.)

(c) more flexible if you need fields in a different order tomorrow - you can have many queries focused on the same source table with different selection and ordering clauses.

(d) performs almost as fast as a direct table operation unless you have a million records; so fast that the difference in performance is usually imperceptible.

(e) doesn't leave your database in dire need of a Compact & Repair after reorganizing a table (and by implication, getting rid of the old one.)

Good luck with whatever you are doing, but it is my advice that sometimes you need to step away and ask what you are REALLY doing. jdraw and I aren't conspiring here - we just both have the same viewpoint. Think first. Then think again. THEN maybe you can start implementing something.

 

[Snowflake68]

Thanks everyone for your replies but i managed to achieve what I wanted in Excel in the end with no complicated vba functions.

Attached is all I wanted to do, it really didnt need to become so complicated.

I then used the attached to update the Operation number in my table. Simples