Good day
I created a system to randomly select jury members from several assigned military units (This is a project I am working for a branch of the military.) What I am currently doing is having the database create a random number using the RND function, then I am having a query pull the top 4 using records based on the random number. The problem is that the list may have records from the same unit twice. For example: 1st Lt XXX and 1st Lt YYY may be pulled, randomly, but they are both from the same unit. Is there a clean way to make sure that the top X are pulled but doing so while keeping the units distinct? The relevant fields at play here are: FullName, Rank, Unit, Sort (which is the random number). I thought about creating a separate query that builds a new random sort number by unit and then applies that number to each record, which works until I get to where I need the random records - If I pull the top 4, for example, and if the unit that has the lowest sort number has lets say 2 person in that rank, it will give me both of those individuals. I hope this makes sense. In the end what I am trying to do is get a random set of 1st Lt where the unit is totally distinct with no repeats.
I created a system to randomly select jury members from several assigned military units (This is a project I am working for a branch of the military.) What I am currently doing is having the database create a random number using the RND function, then I am having a query pull the top 4 using records based on the random number. The problem is that the list may have records from the same unit twice. For example: 1st Lt XXX and 1st Lt YYY may be pulled, randomly, but they are both from the same unit. Is there a clean way to make sure that the top X are pulled but doing so while keeping the units distinct? The relevant fields at play here are: FullName, Rank, Unit, Sort (which is the random number). I thought about creating a separate query that builds a new random sort number by unit and then applies that number to each record, which works until I get to where I need the random records - If I pull the top 4, for example, and if the unit that has the lowest sort number has lets say 2 person in that rank, it will give me both of those individuals. I hope this makes sense. In the end what I am trying to do is get a random set of 1st Lt where the unit is totally distinct with no repeats.