Results 1 to 5 of 5
  1. #1
    kid is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2020
    Posts
    20

    Group Numbering based on seniority

    I would like to auto generate group numbers. I allotted their seniority by their date of join and date of birth by a macro.
    Now I want number their group and their membership.

    Here is the concept:

    I want update two columns GROUP and MEMBERSHIP as the following criteria.

    > A group has one admin, one moderator and 5 members

    -> Based on the seniority First n members are admin for the each group next n members are moderator for the each group and the following n members are members.
    (the value of n can get from form combo box (n- No of Group need)

    Example:
    If I want 5 groups then GROUP column should be updated as
    GROUP MEMBERSHIP
    Group1 Admin1
    Group2 Admin2
    Group3 Admin3
    Group4 Admin4
    Group5 Admin5
    Group1 Moderator1


    Group2 Moderator2
    Group3 Moderator3
    Group4 Moderator4
    Group5 Moderator5
    Group1 Member11
    Group2 Member21
    Group3 Member31
    Group4 Member41
    Group5..... Member51 Like wise....

    for members only the increment number is followed by the group number.

    How can I do with VBA..?

    I attached my file here.GroupCreate.zip

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi there,

    I think this is what you wanted to achieve, please note that it needs error checking and it could probably be streamlined but I was in a bit of a hurry.

    Let me know if it OK.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well, here is my version.
    After I saw Vlad's code, I like his better (cleaner and shorter than mine).

    I had lots of interruptions while writing my code... yeah, yeah, that's it.....too many interruptions

    And it needs error checking....


    Note that the top two lines on EVERY code module should be
    Code:
    Option Compare Database
    Option Explicit
    Also, "Group" is a reserved word in Access and shouldn't be used as object names.
    Attached Files Attached Files

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I like your version Steve, I prefer keeping things "compact" inside loops whenever I can instead of the more "sequential" approach I used. Initially I was going to attempt to do all three types of membership within one loop by using rst.move Me.combo1 (number of required groups from combo) but it was getting late and I wasn't sure I was going to get it right ...

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    kid is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2020
    Posts
    20
    Guys..Both are . Thank you Dears...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sequential Numbering based on Criteria
    By BNeff in forum Access
    Replies: 3
    Last Post: 05-17-2019, 02:55 PM
  2. Restart Page Numbering at Group Level
    By laniebe in forum Reports
    Replies: 1
    Last Post: 05-31-2017, 08:00 AM
  3. Replies: 7
    Last Post: 11-18-2016, 12:14 PM
  4. Replies: 3
    Last Post: 04-28-2015, 02:16 AM
  5. Seniority List Query with Two Date Fields
    By Nuke1096 in forum Access
    Replies: 5
    Last Post: 06-03-2014, 10:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums