5% random sample per user

[Linda2431]

Good morning! I am a basic Access user -- unfortunately I do not have experience programming. Our department performs audits. I need to write a query to get a 5% sample per UserID. Each UserID has many records in a table, and I need to pull a 5% sample per User. If a User has only 1 record in the table, I will need to make sure that record is included in our audit sample. Currently I have an Import database, which I import a spreadsheet of many loans, and the UserID's who have worked on the loans. I need to pull a 5% sample per UserID, and export that into a database where our Auditors perform the audit. I have this database set up, but the process where we pull the 5% sample per UserID is very clunky (this is a database I inherited). I am hoping some experts will be able to assist. If you would like to see what is currently being used, let me know and I can provide a sample. If you know of some programming that can be done, please explain it to me in detail since I'm not familiar with programming -- I just mainly putt queries together in a macro to come up with my results. Thank you very much -- I very much appreciate all the expertise on this site!!!!!

 

[jdraw]

I don't work with macros, but suggest you post the database you have so readers can take a look if they wish.

 

[Linda2431]

I've attached a sample database - both in .accdb format and .mdb format. I've also attached a sample spreadsheet to be used for import.
1. Open the database form.
2. Click the first button. That brings up a blank spreadsheet.
3. Copy/paste the 3 columns from the spreadsheet into the database.
4. Go back to the form - select the second button.
5. This chugs through the queries and imports the 5% sample per user into a table I've created in the same database (tblAuditResultsDaily). I usually import into a different database, but changed so it imports into this one.

I'm SURE there must be an easier way to do this -- any suggestions are much appreciated!!!!!! Thank you very much!!!!!!

 

[Linda2431]

If there is code that will give me the needed results, can any the experts on this Forum let me know? I have spent the afternoon reading about VBA Code, but haven't become an expert in the last couple of hours . . . .

 

[CJ_London]

On what basis is your 5% selected?

If you have a table of 101 records with just two users, one user with one record and the other with 100 records are you wanting to select the one record from the first user and 5 records from the second user?

If this is the case, then on what basis is the 5 records for the second user selected? Is it the 1st, 2nd ..5th record or 1st,20th..99th record for example.

In your query you can select the top 5% but you would need to create a subquery to limit by userid - see attached. To provide further suggestions, would need to know the basis of selection - it may by just a matter of sorting the query by some random field

 

[Linda2431]

We just need a random 5% sample for each UserID. I use this macro for many different spreadsheets. Sometimes there is only one or two UserID's, sometimes there are 20 UserID's. We do a 5% audit for each UserID. Do you think the Top 5% would work? What top 5% would it look at? Thank you for your response -- I appreciate your assistance!!