Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Random data extract

Posted on 2003-03-27
5
Medium Priority
?
192 Views
Last Modified: 2012-08-13
I have a table of information with 4207 records with a couple of fields in MS Acces.. (These are the fields employee number,first name, last name and address).  I would like to create a query that will randomly display 25% of employee's the data. Can someone  help me with this?

I would greatly appreciate it.
0
Comment
Question by:jadeera
5 Comments
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 8221530
first the easy part
to get a random value:

Dim rndbase As Integer
Dim rndvalue
rndbase = DCount("[employee number]", "employee table")
Randomize    
rndvalue = Int((rndbase * Rnd) + 1)

this code get us a random value out of the total recordset
the question now is, what method you prefer to get 25% distinct values.

if you can add a check-field to your employee table
this would be a elegant possibility
in this case you can write a do..while loop on the sum of checked records reaching 25% of total records(= rndbase)
in the do-loop you set the checkfield in the recordnumber rndvalue to true

a filter on the checkfield gives you the randomly selected 25% of employee records

hope you can add this checkfield

regards, Franz
0
 
LVL 6

Accepted Solution

by:
TheAmigo earned 1000 total points
ID: 8221678
To write it as a query, try this:

SELECT TOP 1051 Employees.EmployeeID, Employees.Name
FROM Employees
ORDER BY Rnd([EmployeeID]);

that will also limit it to unique people.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8221786
You can also use TOP 25 PERCENT rather than TOP 1051 :)
0
 
LVL 6

Expert Comment

by:TheAmigo
ID: 8222468
I'll have to remember that.  I found that TOP DCount("field","table")\4 doesn't work, but didn't know about PERCENT...
SQL isn't (yet) one of my strong points. :)
0
 
LVL 18

Expert Comment

by:1William
ID: 9240914
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept question, points to TheAmigo
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

1William
EE Cleanup Volunteer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question