Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Non-Repeating random between per unique ID

Copper Contributor

Hello 

 

Per this solution (here), can it be brought into Access... The memory Limit of the machine means I cannot run the macro in Excel. It works on a limited number of records. 

 

Basically, for each line where the trans_id is the same, I need a non-repeating random number between 1 and 25 (e.g. 1000129 would have 4 unique numbers between 1 and 25).

 

AdamArends_0-1677538314631.png

 

Thanks

4 Replies
What part would you like help with? You've stated a requirement. Is it your plan to have some create a VBA function for you, or propose a SQL solution?

@George Hepworth Hey, thanks for the response. I would take either, really. If there is a for each SQL statement that works, that would be fine and less coding to deal with. 

 

Thanks

@AdamArends 

 

For starters, see how well the ranking queries explained here can handle your requirement.

@AdamArends 

here is a demo db.

open form1.

on design view, see the Code on the Click event of the button.

also see modRandBetween.