Results 1 to 8 of 8
  1. #1
    SpicyGrandpa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Location
    Canada
    Posts
    4

    Assigning the same Sequence Number to Multiple Records

    1st off this is my first post ever! This forum has been helpful solving any issues I've had so far through diligent searching. This was the only one i couldn't find... Maybe not searching with the right terms? If its already answered let me know.


    Not sure yet what i would need to make this happen, which is why i posted in general access questions, vs specific to macro or query.

    I have a big explanation behind exactly what i want to do, but figured it best not to make the 1st post on the question a massive undertaking.

    Quick version:
    Lets say i have imported 400 records in order of sequence from excel. But without a sequence/group number.

    I want a unique sequence number with an interval of 30 records (for instance) instead of every record having a unique sequence, counting down from 1.

    The idea behind it, is to show what group the process is currently on. The carts are sorted in order of date (column not shown) And are to be done roughly in order (within a margin of 30 for instance)
    The group # would be displayed. If an item is scanned and it falls outside of the current sequence (ie. late, or early) then an alternate process is to follow. That is no problem. Only issue is getting that group # assigned to the records.

    Table example (Being compressed to 8 records instead of 60)

    EX of what i currently get:
    ID QTY Cart# Data Group#
    1 18 654664 TMP/MPL/WC 1
    2 5 980734 TMP/MPL/WC 2
    3 4 726354 TMR/MPL/WC 3
    4 2 19385 TMP/MPL/WC 4
    5 1 762098 TMR/MPL/WC 5
    6 3 892376 TMP/MPL/WC 6
    7 3 927843 TMP/MPL/WC 7
    8 2 17684 PAL/MPL/WC 8

    EX of what i want:
    ID QTY Cart# Data Group#
    1 18 654664 TMP/MPL/WC 1
    2 5 980734 TMP/MPL/WC 1
    3 4 726354 TMR/MPL/WC 1
    4 2 19385 TMP/MPL/WC 1
    5 1 762098 TMR/MPL/WC 2
    6 3 892376 TMP/MPL/WC 2
    7 3 927843 TMP/MPL/WC 2
    8 2 17684 PAL/MPL/WC 2

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so using your example, (which doesn't mention sequence) these are sequenced in blocks of 4?

    If this is the case, you can use this formula

    (([ID])-1)\4)+1

  3. #3
    SpicyGrandpa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Location
    Canada
    Posts
    4
    You got it bang on!

    I just adjusted your formula example to the number of records in my query. BINGO!

    You sir are fantastic. Thank you so much!!!

    ((([ID])-1)\30)+1 is what it looked like in the end.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the other one you might want is a count per sequence
    ID QTY Cart# Data Group# Count#
    1 18 654664 TMP/MPL/WC 1 1
    2 5 980734 TMP/MPL/WC 1 2
    3 4 726354 TMR/MPL/WC 1 3
    4 2 19385 TMP/MPL/WC 1 4
    5 1 762098 TMR/MPL/WC 2 1
    6 3 892376 TMP/MPL/WC 2 2
    7 3 927843 TMP/MPL/WC 2 3
    8 2 17684 PAL/MPL/WC 2 4

    in which case try

    (([ID]-1) Mod 30)+1

  5. #5
    SpicyGrandpa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Location
    Canada
    Posts
    4
    you read my mind.
    Thats perfect.

    Is it possible to have group numbers follow a criteria?
    Such say there's 100 records. There's a column showing a color. (Blue and Green)
    the 1st 75 are Blue. The other 25 are Green.
    Is it possible to have the groups stick within colors?
    So they would go up to 30, UNLESS there is a color change.
    So the 3rd group of blue would end at 15 records. then start on green.
    Instead of there being 15 blue and 15 green in one group? Just spit balling to see if its a thing. If not all g. Just figured i'd poke a little.

    EX:

    ID QTY Cart# Color Group# Count#
    72 18 654664 BLU 3 12
    73 5 980734 BLU 3 13
    74 4 726354 BLU 3 14
    75 2 19385 BLU 3 15
    76 1 762098 GRN 4 1
    77 3 892376 GRN 4 2
    78 3 927843 GRN 4 3
    79 2 17684 GRN 4 4

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would need to see a better example, the example you have does not match your text

    but if you just want the first 75 of each group to be blue you could use an iif statement

    iif(count#<75,"Blue","Green")

    Note that Group and Count are reserved words and you should not use # in field names - doing so can cause inexplicable results or errors. Suggest use something like Cart, GroupNo, GroupCount

  7. #7
    SpicyGrandpa is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2018
    Location
    Canada
    Posts
    4
    Sorry about that, What i meant to display, was the table starting at the 72nd entry, going up to the 79th to highlight the crossover between groups.

    But I understand your answer. I think what you've provided me will do the trick. Thanks again so much!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, don't think I quite understand but so long as you do, that's the main thing.

    If you want to display the physical colours rather than text, then investigate the use of conditional formatting (for forms and reports). You can apply the formula there

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

Similar Threads

  1. Replies: 3
    Last Post: 12-22-2016, 12:54 PM
  2. Sequence number in query use a starting number
    By fgwapo in forum Programming
    Replies: 1
    Last Post: 09-21-2014, 12:44 AM
  3. finding the before sequence number
    By Gilbert in forum Queries
    Replies: 1
    Last Post: 07-28-2014, 04:56 PM
  4. Show next number in sequence
    By cbrsix in forum Programming
    Replies: 1
    Last Post: 08-15-2013, 04:09 PM
  5. Enter new number in sequence in PO
    By tchirpich in forum Access
    Replies: 30
    Last Post: 12-30-2011, 11:24 AM

Tags for this Thread

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