Pull Random Data

Carrie

Active Member
Joined
Nov 20, 2002
Messages
418
Is there anyway to get Access to pull random data?

For instance: I am doing an audit of calls that have been closed. Well, I need it to randomly select data from the closed calls so that I can audit it.

Thanks,
Carrie
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
I suppose you could put a random calculation formula in a query to decide whether or not to audit it.

How would you like it to work? What percentage do you want audited?
 

Carrie

Active Member
Joined
Nov 20, 2002
Messages
418
We are needing 10% audited right now.

Here is the query I have so far:

SELECT Call.Call_Num, Org.OrgName, Call.ClosedDate
FROM (Call INNER JOIN Org ON Call.Org_ID = Org.Org_ID) INNER JOIN zSysUser ON Call.ClosedBy = zSysUser.UserName
WHERE (((Call.ClosedDate)>=#2/18/2004# And (Call.ClosedDate)<=#2/19/2004#) AND ((zSysUser.Queue1) Like ".FLS*" And (zSysUser.Queue1) Not Like "*HW") AND ((Call.Project)=0) AND ((Call.IsClosed)=-1));


Let me know.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
Similar to the calculation in Excel, create a similar calculation in Access in a query using the RND function.
 

Carrie

Active Member
Joined
Nov 20, 2002
Messages
418

ADVERTISEMENT

I am not good with Access...is there a way you could explain the RND function and how to use it?

Sorry, I am a beginner with Access.
 

Carrie

Active Member
Joined
Nov 20, 2002
Messages
418
This is what I see in the thread you referrenced.

SELECT TOP 20 PERCENT tblTest.TestData
FROM tblTest
ORDER BY RandomID([TestID])


It says top 20 percent. I want a random percentage not just the top of it. Meaning if there were 100 rows of data I would want 10 rows extracted randomly. Not the top 10 rows...is that what this will do?

Also, the tblTest.TestData...does this represent the field that I want randomly selected? So in my case I want a random 10% of the call number so it would be:

SELECT TOP 10 PERCENT Call.Call_Num
FROM Call
ORDER BY RandomID([CallID])

I am pretty fuzzy on how to write code but I am pretty good at the design view...just not when I have to add stuff.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
You need to copy that code for the Random function into a public module so that you query can access it. Then in your query, if you go into the SQL View instead of Design View, you should be able to edit the query appropriately. Changing it to "Top 10 Percent" should work.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top