Prev: a
Next: Check if folder path exists
From: Paul on 13 May 2010 08:34
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
From: ghetto_banjo on 13 May 2010 08:51

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.
From: Arvin Meyer [MVP] on 13 May 2010 08:57
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" <pepeexcel(a)hotmail.com> wrote in message
news:aecdec8e-bdae-4373-a741-fa213515ccd8(a)k42g2000yqb.googlegroups.com...
> 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


 | 
Pages: 1
Prev: a
Next: Check if folder path exists