Random records based off of number of records

[Soupy8728]

Good morning all,

Here is what I'm trying to do:

Table has over 100,000 records. Within the table there is a field called Department and it contains various departments (Department A, Department B...etc). I need to run a random sample based off the number of records each Department has. For example, if Department A has more that 100 records but less than 1000 records, I need a random sample of 35 records. If Department B has more than 100 records but less than 1000 records, I need a random sample of 35 records. I ran a query to count the number of records each department has but I just can't figure out how to randomly select only X amount of records based off those numbers. I hope this makes sense. Thank you in advance for your time.

[Micron]

Something like this perhaps?
https://answers.microsoft.com/en-us/...b-102e151a500f
Note that you require an autonumber (or other number field that is unique and probably consecutive).
I suspect there are other logical considerations that you'll need to code for, so that this is done according to decision making. F'rinstance you have not said what happens if there are more than 1000 records for a department. In that case, do nothing? Also, I imagine this requires a loop to iterate over all the departments.

[orange]

Here is a link within this site dealing with Random Records. IT may offer some insight to a solution.

[Soupy8728]

Something like this perhaps?
https://answers.microsoft.com/en-us/...b-102e151a500f
Note that you require an autonumber (or other number field that is unique and probably consecutive).
I suspect there are other logical considerations that you'll need to code for, so that this is done according to decision making. F'rinstance you have not said what happens if there are more than 1000 records for a department. In that case, do nothing? Also, I imagine this requires a loop to iterate over all the departments.

Thank you. I have unique numbers so that's not an issue. There are other logical considerations as you have stated which I believe I will be able to address once I get this initial problem figured out. In my query where I counted the records, I created another calculation field to give me my "Sample size". For instance, if the count of records for Department A was between 100 and 1000, then it assigned the Sample Size of 35. Now what I need to do is figure out how to tell access to take that "Sample size" number and randomly select that amount of records from all of Department A records. Additionally, that "Sample size" number can vary so if they have less that 100 records, it will be 15, if they have more than 1000 records it will be 75. Hope that helps.

[Bulzie]

Just thinking of the looping, you will need to do 2 loops, one for the Depts and one for the number of records for each Dept. Once you know the number of records you need for a Dept, you would do a loop though those Dept records to select a random record and write it to another table(or you can add a field to your table for this process to mark the selected records and just blank that field out each time you run this process), then update your counter to x=x+1, compare it to your total needed and keep going until you get the total needed, then loop again for the next department. Also each time you select a random record, you need to check (Dlookup will work) to make sure you did not already select that record and if you did, loop back to get another record.

[Micron]

I say 1 loop for department only. Calculate the number of records per dept and use that in a Select query statement with Top N predicate (sub query).

[Soupy8728]

I say 1 loop for department only. Calculate the number of records per dept and use that in a Select query statement with Top N predicate (sub query).

Thank you but I'm not sure how to do that. I was hoping it would be a lot easier for my experience level. Hahaha...

[Welshgasman]

Take it one step at a time.
Break it down into small steps, and work on each in turn.

I would start with
Code to print random records with a hardcoded number of times, but still using a variable.
I would probably store the recordID selected in a table, so that it could not be selected more than once. You could then use that table to produce all your records as the source.

When you have that working you need to surround that code with a loop to process each record in turn from a recordset. That recordset would also hold the number of record to produce (which you say you already have).

Not sure how you are going to get truly random records though.

[Soupy8728]

Take it one step at a time.
Break it down into small steps, and work on each in turn.

I would start with
Code to print random records with a hardcoded number of times, but still using a variable.
I would probably store the recordID selected in a table, so that it could not be selected more than once. You could then use that table to produce all your records as the source.

When you have that working you need to surround that code with a loop to process each record in turn from a recordset. That recordset would also hold the number of record to produce (which you say you already have).

Not sure how you are going to get truly random records though.

Thank you for your patience and your guidance. I was able to generate the random records part fairly easy but only with a hard code number such as Top 100 or Top 10%. I was trying to see if there was a way to use my record count query to populate that hard code number automatically as my records change on a daily basis.

[orange]

Soupy8728,

Did you look at my sample random records and selection mentioned in post #3?

[Welshgasman]

Thank you for your patience and your guidance. I was able to generate the random records part fairly easy but only with a hard code number such as Top 100 or Top 10%. I was trying to see if there was a way to use my record count query to populate that hard code number automatically as my records change on a daily basis.

Not really random then?
You would build your sql with the value coming from your source query as a recordset.

[Soupy8728]

Soupy8728,

Did you look at my sample random records and selection mentioned in post #3?

Yes but that is beyond my capability. Thank you tho.

[Micron]

We don't know what your experience level is with this and there isn't enough info to create anything. What are you expecting from us, exactly? Guidance or a tangible solution, or something else? Is posting a zipped db copy an option?