How can I apply the same identifier (8 digit random number) to each set of ten rows?

[John Crutchfiel]

Hi,

I have a query that pulls information I need and lists out certain "REP ID"s. I need some sort of update script the will take the first ten and assign them all the same 8 digit number in another column called "Sheet Barcode". It may not always end in exactly ten rows, but I would need a new number assigned for the following six, for example, as long as its still the same REP ID. Please see below for an example of what I am looking for.

Auto REP ID Sheet Barcode
1 22687 12345678
2 22687 12345678
3 22687 12345678
4 22687 12345678
5 22687 12345678
6 22687 12345678
7 22687 12345678
8 22687 12345678
9 22687 12345678
10 22687 12345678
11 22687 23456789
12 22687 23456789
13 22687 23456789
14 22687 23456789
15 22687 23456789
16 22687 23456789
17 22687 23456789
18 22687 23456789
19 22687 23456789
20 22687 23456789
21 22687 34567891
22 22687 34567891
23 22687 34567891
24 22687 34567891
25 33204 45678912
26 33204 45678912
27 33204 45678912
28 33204 45678912
29 33204 45678912
30 33204 45678912
31 33204 45678912
32 33204 45678912
33 33204 45678912
34 33204 45678912


The randomly generated number can be anything just as long as it's a number and is not duplicated. Thanks in advance for any insight!!

[ADezii]

I just wish to clarify a few points before proceeding:

1. Generate a Unique, 8-Digit, Random Number for each Series of 10 [REP ID]s.
2. If the [REP ID] is the same, a new Random must be generated for each Interval of 10 Records, specifically at: Records 11, 21, 31, etc.
3. Anytime the [REP ID] changes, so does the 8-Digit Random Number.
4. Does the Randomly Generated Number need to be formatted to 8 Digits if it is less than 8 Digits in length, namely: 00009823, 00239634, 00000203, 09987600, etc.?
5. Are the above statements correct?

[Rabbit]

Rnd(([RepCode] & (([AutoID] - 1) \ 10)) * -1) AS PseudoRandomNumber

Edit: Nevermind, guess that doesn't work. I thought it's supposed to give the same number if the seed doesn't change.

Edit 2: Nevermind again, I was right the first time. It just has to be a negative number.

[NeoPa]

Unfortunately Rnd() can return the same number even if the seed is different.

I would actually suggest using code to process through the records. Every time a new value is determined a check should be done on the existing data in the table to ensure the value has not already been used. If it has, simply drop it and go for another one. Rnd() with no seed parameter should provide the next random number in sequence from the previously passed seed.

[ADezii]

@NeoPa - Isn't it a little more complicated than just generating another Random every time the [REP ID] changes? A new Random is also needed for every new series of 10 Records for any given [REP ID], and for any remaining Records after a series of the last 10 (Post #1). It could also be that I am over complicating matter again! (LOL).

[NeoPa]

ADezii:
It could also be that I am over complicating matter again! (LOL).

No my friend. You simply missed a clever trick of Rabbit's.

If you look carefully at the seeding within the Rnd() call you'll see that it changes after every ten records (related to [Auto ID]).

If it's a post from Rabbit, you can expect something pretty clever and fiddly in there somewhere ;-)

[ADezii]

Thanks NeoPa, he is, after all, a rascally Rabbit! (LOL).

[Rabbit]

My bad, I missed the part at the end of the post about it being unique. A function would be the only way to guarantee uniqueness. Although I do wonder about the purpose of all this and whether a random number is truly needed.

[ADezii]

I'm probably off on a Tangent again, but I was thinking along the lines of a very simple Function to generate a Random Number between 1 and 99,999,999 as a Formatted String. The odds of any Duplication would be a rarity, to say the least.

Public Function fGen8DigitRndNum()
Randomize
 
fGen8DigitRndNum = Format$(Int(Rnd * 99999999) + 1, "00000000")
End Function

[NeoPa]

John, There's enough info here to do the full job properly. Let us know where you are with that, how you get on or if you need more help on the matter.

Wascally Wabbit:
Although I do wonder about the purpose of all this and whether a random number is truly needed.

I read the request for a random number as indicating that it simply has no specific requirements. Nothing in the question says it needs to be unpredictable in any way. Simply that it's different from any of the other values used. As such, there is no requirement to use Rnd(), though it seems to fit the bill quite nicely ;-)

[Rabbit]

Well, it's just that if randomness is not truly needed. You can achieve the same results by using RepCode & ((AutoID - 1) \ 10)

ADezii, you would still need to keep track of the RepID and if 10 records have already been issued that random number. Plus Rnd by itself will move on to the next random number.

[NeoPa]

I'm sure what you had before, or a minor variation of it, is fine for producing the requisite value. It's now just a question of ensuring it's used up to ten times etc.

Int(Rnd('-' & [RepCode] & (([AutoID] - 1) \ 10)) * 100000000)

This produces a value between 0 and 99,999,999.

[John Crutchfiel]

Thanks, everyone! I did not expect so much help in that short amount of time. I will try the Rnd() function tomorrow and let you know how it comes out. You are correct, true randomness is not necessary I just need a unique identifier for each set of ten records per Rep ID that also changes for each change in Rep ID itself. This logic seems like it would work. I will keep you posted and thanks again!!

[John Crutchfiel]

This worked great! Although strangely it produces seven digit numbers sometimes, instead of eight. It's not a big deal though and I can definately make it work. For reference this is what I put into the access query:

Expr1: Int(Rnd('-' & [REP ID] & (([AutoNumber]-1)\10))*100000000)

THANKS AGAIN!!

[NeoPa]

See post #9 for what to do to ensure the number strings are 8 digits (Format()). This doesn't ensure that the lowest number is 10,000,000 mind you. That was never part of the request. Possible of course, but we try to answer the question as submitted.