Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Table records numbering

Discussion in 'Information Technology' started by dc, Jul 28, 2006.

  1. dc

    dc
    Expand Collapse
    Guest

    Hi, I have a query where I append records from other sources which I would
    like to number 1-20 (or however many). I understand that using the
    autonumber is not truely an option but is there something that I can either
    put in the append qry or in the field properties on the table?
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    Are you wanting to start over with each new group of records? I.E. if you
    import 20 today from Source A, they are 1 thorugh 20. If you import 20
    today from Source B are they 21 through 40, or 1 through 20 again?

    And how about from A tomorrow? If you import 20 from A tomorrow will they
    be 21 through 40 or 1 through 20 again?

    Either way, you very likely have to write a littel vb function to go back
    through and do your numbering, since AutoNumber doesn't seem to meet your
    requirement.

    HTH
     
  4. dc

    dc
    Expand Collapse
    Guest

    Hi Rick, I am thinking that I would like to have additional appended records
    continue with 21 forward. If the table is empty have the numbering start at
    1 again. Is that possible?

    "Rick Wannall" wrote:

    > Are you wanting to start over with each new group of records? I.E. if you
    > import 20 today from Source A, they are 1 thorugh 20. If you import 20
    > today from Source B are they 21 through 40, or 1 through 20 again?
    >
    > And how about from A tomorrow? If you import 20 from A tomorrow will they
    > be 21 through 40 or 1 through 20 again?
    >
    > Either way, you very likely have to write a littel vb function to go back
    > through and do your numbering, since AutoNumber doesn't seem to meet your
    > requirement.
    >
    > HTH
    >
    >
    >
    >
     
  5. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    If you are appending one by one ( I can't see the original post from here)
    you can append a record, and then update that record like this:

    Update MyTable Set MySequenceNumber = ((Select Max(MySequenceNumber) From
    MyTable) + 1)
    Where MySequenceNumber Is Null

    append, update, append, update, append, update. It only works if you're
    appending a single record at a time.

    If you append in bulk, many rows at a time, the only thing I think would
    work is to write some code and use a recordset. First append. Then get the
    maximum existing MySequenceNumber. In code you can use DMAX() for this, or
    do a recordset based on "Select Max(MySequenceNumber) From MyTable".

    Next, make a recordset based on "Select MySequenceNumber from MyTable Where
    MySequenceNumber Is Null". Define a variable, NextSeq, long. Dive in:

    NextSeq = nz(dmax(....) ,0)
    rst.MoveFirst
    do until rst.eof = true
    NextSeq = NextSeq + 1
    rst.fields("MySequenceNumber") + NextSeq
    rst.update
    rst.movenext
    loop

    "dc" <dc@discussions.microsoft.com> wrote in message
    news:76EDCF3D-B64B-482C-AD3B-427081C6CAC4@microsoft.com...
    > Hi Rick, I am thinking that I would like to have additional appended
    > records
    > continue with 21 forward. If the table is empty have the numbering start
    > at
    > 1 again. Is that possible?
    >
    > "Rick Wannall" wrote:
    >
    >> Are you wanting to start over with each new group of records? I.E. if
    >> you
    >> import 20 today from Source A, they are 1 thorugh 20. If you import 20
    >> today from Source B are they 21 through 40, or 1 through 20 again?
    >>
    >> And how about from A tomorrow? If you import 20 from A tomorrow will
    >> they
    >> be 21 through 40 or 1 through 20 again?
    >>
    >> Either way, you very likely have to write a littel vb function to go back
    >> through and do your numbering, since AutoNumber doesn't seem to meet your
    >> requirement.
    >>
    >> HTH
    >>
    >>
    >>
    >>
     

Share This Page