?
Solved

Random data extract

Posted on 2003-03-27
5
Medium Priority
?
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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