Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2005
    Posts
    23

    Unanswered: ReOrder Numbers Chronologically

    I have a table that stores a list of reports and the order in which they need to be completed.
    ex.

    REPORT Rank
    A 1
    B 2
    C 3
    D 4
    E 5

    After working on the reports two get completed. Lets say Report B and D. The table now looks like:
    REPORT Rank
    A 1
    C 3
    E 5

    Is there a way to have something run that would adjust the Rank of report C to read 2 and report E to read as 3.
    EX

    REPORT Rank
    A 1
    C 2
    E 3

    Thanks in advance for your help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Running sum/count on your report should be able to handle this.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2005
    Posts
    23
    When I say reports I don't mean reoprts in Access but operational reoprts that need to be created in some other system. This database is just a means of tracking the work load. Reports in this case refers to the title of a report that will be created in the future most likely in Excel and the rank is the order in which the should focus their efforts. The team looks at a form based off another table for their workload and I adjust the rank on another table through append and update queries. What I need to do is have my table with the reoprt name and rank reorganize the rank when an exsisting report gets removed. Thanks
    Last edited by DennisG; 12-16-08 at 19:09.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If this is a one time thing, I'd push it in to a temp table with an autonumber field, then update my original table with the rank generated by the autonumber.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2005
    Posts
    23
    This in a perfect world would happen a couple of times a day in an on open event when I open the form that allows me to set the rank.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I would still argue that this is a display issue.

    If you disagree then Teddy has already presented you with your best alternative option.
    George
    Home | Blog

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ I don't agree. Teddy's suggestion is for a one-off idea. If it is being run frequently, pushing the data to Temp tables = file bloatation and an overly complex solution for such a minor thing.

    I would just use VBA to loop through the data and renumber the Rank field. No biggie. I would do that even if it was a one-off thing tbh.

    Mind you, if there are squillions of records, I'd go with the temp table idea of Teddy's

    I also agree that this is really a display issue. I see no reason to do it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by DennisG
    This in a perfect world would happen a couple of times a day in an on open event when I open the form that allows me to set the rank.
    And I agree with both George and Teddy also. Just taking it as it seems, the Ranking number just says that Rank 1 gets processed before Rank 2. So even after you have completed some and some original Ranks are then used the remaining ranks still are in order. Take your example:

    After working on the reports two get completed. Lets say Report B and D. The table now looks like:
    REPORT Rank
    A 1
    C 3
    E 5

    Now, with the remaining ranks being 1, 3 and 5...they are still in order even with missing Rank numbers. Seems a lot of work that in my view doesn't really need to be done. What about the integrity of the Original Ranks? Does that even matter? If not, your Ranks are still in order. Just my thoughts on it.

    have a nice one,
    BUD

  9. #9
    Join Date
    Sep 2005
    Posts
    23
    I agree that it is just for display purposes however I feel I need to do it due to volume. During the course of a month the report turnover is about 150 reports. This database will be in use for some time so theoretically I could have 40 reports with ranks from 1 to 450, with the spread only increasing.

    Below is my attempt at a solution but there are a couple of problems with the code.
    1. only the recordset is updated not the table.
    2. I am a newbie to this so I am sure I took the longest way to get there and didn't follow "The rules" of writing.

    Public Sub reorder()

    Dim cdb As CurrentProject
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim c, oc, cnt As Integer

    'sql = "Select * FROM tbl_Rank"
    rs.Open "Select * FROM tbl_Rank", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    cnt = rs.RecordCount
    c = 1
    Start:
    Do Until rs.EOF
    If rs.Fields("Rank") = c Then
    c = c + 1
    rs.MoveFirst
    Else
    rs.MoveNext
    End If
    Loop
    fmc = c
    rs.MoveFirst
    oc = c
    c = c + 1
    Do Until c = cnt + 1
    Do Until rs.EOF
    If rs.Fields("Rank") = c Then
    rs.Fields("Rank") = c - 1
    c = c + 1
    oc = oc + 1
    rs.MoveFirst
    Else
    rs.MoveNext
    End If
    Loop
    rs.MoveFirst
    oc = oc + 1
    c = oc + 1
    Loop

    If fmc = rs.RecordCount Then
    Exit Sub
    Else
    c = fmc + 1
    GoTo Start
    End If


    rs.Close
    Set rs = Nothing
    End Sub

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id run an occasional batch process whihc renumbered things (if required)
    otherwise I wouldnt' give two hoots whether the numbers are contiguous, providing the numbers are sorted in ascendign order it doesnt matter that they are prioritised as 1,3,5,6,7,8,9,20,35........

    it doesn't matter to me because the relative rank remains the same.
    for housekeeping I may decide to, say nightly reassign the priority ranking to be contiguous, or to make room for lower priority values). but it seems daft to me
    I suspect there may well be a better solution using say a target date and ordering on that

    if I must have a contiguous list then I'd cheat and run off a report, or dump the records in a list box, or if I was really minded to do somethign fake the whole form as do my own calculations based on sme predetermined sort order. you could fake page up page down etc but that seems a heck of a lot of work for somethign as trivial as this.

    why does it matter that the index is not in contiguous order...?

    and please dont' say because my boss said so, or thats the way we do things round here....... its amazing how often such requirements pop up because thats the way we've always done it.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2005
    Posts
    23
    The reason is completely astetic but I need to have things neat and clean.

    When I started this little section, I didn't think it would be as hard as it has turned out for me. If I just left it as, it would still work but I have robbed myself the oppertunity to learn something I don 't know that I may really need in the future.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    450 records is nothing. Running Sum/Count on your report will handle this in a beat.

    Give it a try - you'll be surprised by the results.
    George
    Home | Blog

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What happens if someone creates a report that excludes "c", for example?

    If this is a PURELY aesthetic value, as you say, then I wouldn't waste the trouble of persisting those values to physical tables.

    I'd be inclined to go with the running count on this one as well. That is the generally prescribed best practices way of numbering records with an incrementing value
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If your intent on repopulating the table with sequencial numbers based on the sort order of "Report" field (ie. A, B, C, D, etc...) here's what I would write (in ADO though and from memory):

    dim rs as adodb.recordset
    set rs = new adodb.recordset
    Dim CCount as integer
    CCount = 1
    dim strSQL as string
    strSQL = "Select * from tbl_Rank Order By Report" (although "Report" is not a good field name - keyword.)
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    if rs.eof and rs.bof then
    rs.close
    set rs = nothing
    msgbox "No records in table!"
    else
    rs.movefirst
    do while not rs.eof
    rs!Rank = CCount
    rs.update
    CCount = CCount + 1
    rs.movenext
    loop
    rs.close
    set rs = nothing
    end if

    Note: you could also sort by the "autonumber" field if you have one. This would always give you a sequential based upon the order of entry of data in the table. Otherwise, ordering by the "Report" field means that you could enter/change Z (for example) to B and the Z record (now B) would have a Rank of 2, regardless if the Z record was entered last.

    The above code should "fly" through 450 records. As pointed out though, your other option is to create a temp table versus repopulate the tbl_Rank as above (which also means no record of past data/order - something to keep in mind.)
    Last edited by pkstormy; 12-18-08 at 23:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •