Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    3

    Unanswered: auto assign a sequnce number in a query in MS access

    emplid SeqNbr
    22222 1
    22222 2
    33333 1
    33333 2
    33333 3
    55555 1

    I have a query which has emplids. I need to add a sequence number that restarts numbering for each emplid. See example of the goal above.

    Please help!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to do that you will need to do it using a mix of VBA ans SQL
    you will need to lock the query the table, find the current value, (0 if not found) and add one to it update the data release the lock

    so you need to look at recordsets
    you need to understand the concepts behind a table lock, unless you will never ever have any conflicts (ie more than one user trying to update the same employee ID data at the same time.
    you need to understand error trapping
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is there another column that will govern the order of the SeqNbr? What differentiates the first 22222 and the second 22222?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2008
    Posts
    3
    The main fields are:
    EMPLID
    EARN_CD
    EARN_AMT
    EARN_BEGIN_DT
    EARN_END_DT

    Basically this is the bonus file that needs to be uploaded into People Soft and People Soft is requiring a sequence for each instance in the bonus file. For example we can have an EMPLID with different Earn_Cd and we need a sequence counter for each instance. I hope I'm making sense. It sounds right in my head when I explain it but not sure if it is coming out right:-) shocked:

  5. #5
    Join Date
    Oct 2008
    Posts
    3
    Any ideas?
    Thanks :-)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You've shown us some more column names but I need to know which of those answer the question in post #2. Can you add the data and columns to what you put in your first post?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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