If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Random Sampling
Hello. I have a table in Access that is updated each day with data
from the previous business day. The table consists of a date, name, type and id. For this example, let's say that the types are Type A, Type B or Type C. I need to take a random sampling of 10 id's for each of the different Types for each Name that is listed for a particular day. For example if 1 agent worked yesterday, I would need to see a random sampling of 10 Type A id's, 10 Type B id's and 10 Type C id's for a grand total of 30. If more agents worked then I would need to have the same for each of them. Does this make sense? 10 random id numbers of each type for each agent that worked on a particular day. Does anyone have any thoughts on this? Any help would be greatly appreciated. Thanks |
#2
|
|||
|
|||
Random Sampling
One way to accomplish this, is to create a query that uses the Rnd() function so that it sorts itself via a random number. Then you can specify that it only returns the TOP 10 records per each Type. I am assuming your id field is a primary key, preferably AutoNumber type. SELECT TOP 10 * FROM YourTable WHERE Type = "A" ORDER BY Rnd(IsNull(ID)*0+1); That would return 10 random Type A records I believe. |
#3
|
|||
|
|||
Random Sampling
I'd do it with 3 queries, one for each type, then if necessary to combine
them, use a union query. So: Create a function in a module: Function GetRandNum(varValue As Variant) As Double Randomize Timer GetRandNum = Rnd(1) End Function The query would look like: SELECT TOP 10 ID, [Name], [Date], Type FROM YourTableName WHERE Type = "A" ' or B or C ORDER BY GetRandNum(ID); Also, note that Name and Date are reserved words and you will have big problems using them, which is why I used square brackets around them. Now the Union query looks like: Select * From Query A UNION ALL Select * From Query B UNION ALL Select * From Query C; -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access "Paul" wrote in message ... Hello. I have a table in Access that is updated each day with data from the previous business day. The table consists of a date, name, type and id. For this example, let's say that the types are Type A, Type B or Type C. I need to take a random sampling of 10 id's for each of the different Types for each Name that is listed for a particular day. For example if 1 agent worked yesterday, I would need to see a random sampling of 10 Type A id's, 10 Type B id's and 10 Type C id's for a grand total of 30. If more agents worked then I would need to have the same for each of them. Does this make sense? 10 random id numbers of each type for each agent that worked on a particular day. Does anyone have any thoughts on this? Any help would be greatly appreciated. Thanks |
Thread Tools | |
Display Modes | |
|
|