Random_Picking_By_Percentage

[sanjeev]

please can anyone help?

i have a table (voter_details) with several fields that tracks voter_id,
voter_name, voter_address and voter_zone.

I want to create a query or macro that getting 20% of voter_id from each
voter_zone on random basis.


For example, if voter_zone 1 has 500 Voter_id I want to list 100 voter_id
If voter_zone 2 has 900 Voter_id I want to list 180
voter_id
And so on.

The Table looks like this table=voter_details
Total Records = 1200000-1250000
Total Voter_Zone = 500-600

Voter_id Voter_name Voter_address Voter_zone
C89126 Ram 190, Ara
Pat-20
DD5490 Lalan H.No 60, Gaya Bih-60
AZ1259 Sita B/B-10, Ara
Pat-20


Hope you can help.
Thanks

 

[John Spencer]

This is a tough one to solve with that many records in the database.
Any solution to do this for all zones in one query at one time could be
slow.

First thing you need is the following VBA function from John Vinson.
Copy it and paste it into a VBA module (the module cannot have the same
name as the function).

'================= Random Number Function for Queries ===============
Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function
'============= End of Random Number Function for Queries ============

For one zone at a time, you could use
SELECT Top 20 Percent Voter_ID
, Voter_Name
, Voter_Address
, Voter_Zone
FROM Voter_Details
WHERE Voter_Zone = "Pat-20"
ORDER BY RndNum([Voter_Zone])

Assuming that you have indexes on the proper fields that should be
fairly quick for any one zone (average of approximately 23,000 records
per zone).

For all zones in one query, the problem becomes more complex and
performance could slow. The best way to handle this might be to add a
field to the Voter_Details table to hold a number from 0 to 4 and then
select on that.

SELECT Voter_ID
, Voter_Name
, Voter_Address
, Voter_Zone
FROM Voter_Details
WHERE Int(RndNum(Voter_Zone)*5) = 0

If you can't add a field to voter_details, then you should be able to
create a new table with two fields Voter_ID and GroupNumber and populate
it with a query like the following. Be aware that the following
solutions will not return exactly 20% of the group, but they will be
fairly close to that number.

INSERT INTO Voter_Random (Voter_ID, GroupNumber)
SELECT Voter_ID, Int(RndNum(Voter_ID)*5)
FROM Voter_Details

Then use that table in the query
SELECT Voter_ID
, Voter_Name
, Voter_Address
, Voter_Zone
FROM Voter_Details INNER JOIN Voter_Random
ON Voter_Details.Voter_ID = Voter_Random.Voter_ID
WHERE GroupNumber = 0

If you needed finer gradations (1 percent for instance) change
Int(RndNum(Voter_ID)*5)
to
Int(RndNum(Voter_ID)*100)

And to get 3 percent you can change the where clause to
WHERE GroupNumber in (0,7,9)
Or any three numbers from 0 to 99 that you wish.

Also if you use the finer gradation, you will be able to come closer to
the exact 20 percent by either including 20 different group numbers or
specifying a range including 20 different group numbers. For instance

WHERE GroupNumber Between 10 and 29

Hope that all this helps you resolve your problem.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================