Help with MS Access query

thank you for your respond, bit  i did not get that

i did not get that

Please clarify what you mean.  What is the "that" you aren't getting?

You need the distinct names:

SELECT TOP 3 PERCENT T.*
FROM 
    (SELECT First([ID]) As SomeID, PersonName
    FROM List
    WHERE List.Picked = False
    GROUP BY PersonName) AS T
ORDER BY Rnd(-Timer()*[SomeID]);

Select allOpen in new window

/gustav

TO :aikimark
I do not understand what should I do, that is why my respond was like that


TO:Gustav
thank you, but i got only one name
SomeID PersonName
244         Steve

Well, one name may represent 3% ...

/gustav

Are there only going to be three names (or there about)?  If so, you can use a UNION query to get the data.  Each Select statement will include a WHERE PersonName ="personnamevalue" for each of the three, or so, names.

If there are a lot of names, then some VBA code would instantiate a recordset variable with the distinct names.  Looping through the rows on that recordset, another query would be run, passing in the current iteration's PersonName value.  Probably the simplest scheme would be to append the Top 3% rows to a table.

this is a thing, every time the input table will be different, today we will have 10 employee, and tomorrow will be only one
and regardless the condition the query should pick up 3% for each employee. I agree, for "known"  "cast" of the employee the logic is like that
get distinct names
select the records with the first name
make a table only for this name
get 3% for this name
make a table with those records
Loop to see if another name is there
select the records with the second name
get 3% for this name
append to existing  table with those records
Loop to see if another name is there
-----do until no more employees


but the file every time is different...

The VBA code will probably look something like this:

set rsUniqueNames = dbEngine(0)(0).OpenRecordset("Select Distinct PersonName From List")
do until rsUniqueNames.EOF
   dbEngine(0)(0).Execute "Insert Into Top3Pct (ID, [Activity id], [Name], Picked, DrawID) " & _
   "SELECT TOP 3 PERCENT ID, [Activity id], [Name], Picked, DrawID " & _
   "FROM list WHERE List.Picked =False And PersonName='" & rsUniqueNames.PersonName & "' " & _
   "ORDER BY RandomNumber ([ID] is null);"

   rsUniqueNames.MoveNext
Loop

Select allOpen in new window

You must create the Top3Pct table.

Try this:

SELECT 
    List.PersonName,
    T.Id
FROM
    List,
        (SELECT TOP 3 PERCENT *
        FROM List As R
        WHERE R.Picked = False
        ORDER BY Rnd(-Timer()*R.[Id])) As T
WHERE
    T.PersonName = List.PersonName
Group By
    List.PersonName,
    T.Id

Select allOpen in new window

/gustav

the query is working, i tested five times, and sometimes i have results which are OK but most of the time same person and nobody else, which is not
thank you very much,
you know, my first effort should be to get 3% of each person. and after i will do the rest

So please, what i need right now, just to get 3% for every name in the table, any idea?

I can't follow. Could you rephrase your comment, please?

/gustav

sorry, wrong posting
was trying to sent to someone else

i do not know what to do
i need to split the table by distinct name, get the the number , let say in this case 3
and run the query by number of distinct names (3) and get 3% for each person

aikimark query too complicated for me... can not handle that

I don't know. I ran a test here - with other data of course - and it returned a selection where I could turn up and down the percentage with success.

/gustav

i counted all records by distinct name

1      2      3      Expr1      Expr2      Expr3
76      162      2      2              5              0

person 1 appears 76 times and 3 % of 76 is 2
person 2 appears 162  times and 3 % of 76 is 5
person 3 appears 2 times and 3 % of 76 is 0

but when i run the query it get me ( of course every time differently)
1   3
2    4
3    0

1   2
2    5
3    0

1   3
2    4
3    0

1   1
2    6
3    0
 the only correct number is a total number of records (7)

I posted VBA code.  It needs to go in a module.

So what you wish returned is always - for these data - this fixed count of IDs for each person:

1:  2
2:  5
3:  0

and for each run, a random selection of IDs for each person?

/gustav

thank you very much

gustav: the problem is --it is not always, sometimes
aikimark: it is kind of complicated for me

This might do it (air code):

SELECT 
    List.PersonName,
    List.Id
FROM
    List
WHERE
    ID IN
        (SELECT TOP 3 PERCENT ID
        FROM List As R
        WHERE R.Picked = False
        AND
        R.PersonName = List.PersonName
        ORDER BY Rnd(-Timer()*R.[Id]))
Group By
    List.PersonName,
    List.ID

Select allOpen in new window

/gustav

it is kind of complicated for me

Without a copy of your database, I can't help you further until you have tested the code I published.  You are welcome to ask questions.  However, your inexperience seems to be a limiting factor in arriving at a solution.  You might want to look at the top Access experts to see if any of them have enabled their Hire Me button.

In a question thread, you are expected to work with the experts.

Thank you for your concern
The database included
RandomSelectionDB.accdb

Look at this updated version of your database.
RandomSelectionDB.accdb

thank you very much, i need some time to check and i will be back to you

thank you very much

You are welcome!

/gustav