Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Thanked 0 Times in 0 Posts

    Numbering Records


    Actually, it's not what you think. I have a series of records, which are alpha-sorted on a field. I wish to assign sequential integer values to another field, in the
    order of the alpha-sort, starting at an arbitrary index
    (perhaps, say, 1 or 11). Alternatively, I need to take
    the results of a query, sort the records so identified,
    and number those.

    Does anyone already have a simple code fragment to demonstrate a solution to this problem (note that I'm using Access97, not Access2000).

    Obviously, permutations of AutoNumber is not what I need.
    Many thanks for a good education :-).

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    Re: Numbering Records

    You could use a function that incremented a global variable.

    Function IncrementCounter() as integer

    gintCounter = gintCounter + 1

    End Function

    The global variable would be set outside the function and the initial value could be set then as well.

    This function could be called from a query that would update the other field.

    Hope this helps

    Richard Aheron

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: Numbering Records

    If I understand the original post correctly, you want to add a numeric sort key to your table, right? Or do you just want to do this in a query? If you only want to do it in a query, the previous post presents a method. You just have to be sure to reset the global variable between each execution of the query--which is one of the drawbacks to global variables.

    If you actually want to imbed the sort key in the table, you need to think through a couple of questions: 1) is the order straight sequential through the entire table, or do you want to sort within groups? In the latter case, the sort key would be repeated within different groups. 2) are you going to need to adjust the sort key as you add new records, and how do you envision doing that?

    I use this approach for things like lists of officers in organizations. The titles may differ between organizations and the relative ranks may differ for the same title. So it's easiest to have a numeric field that says, in effect, sort Executive Secretary higher than Director in this organization but lower in the other organization. In this example, each organization would have its own set of sort keys from 1 to however many officers it had. Is that the kind of thing you're trying to do? If so, it takes a bit more than a simple query.

Posting Permissions

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