Help with MS Access query

I have a table called List
ID autonumber
Activity id text
Name
Picked yes/no
DrawID number

please see attached excel fileList.xlsx


SELECT TOP 3 PERCENT *
FROM list
WHERE List.Picked =False
ORDER BY RandomNumber ([ID] is null);

the query is working fine, it returns 3% of the total number of records. Because it random selection sometimes it returns only for Peter or only for Steve or both and never for Mary.
if i have let say 120 records then 3 % of 120 is 4. That is what i am getting now

Now where i need your help: i need to modify to make sure every distinct name should get 3 % (not from whole count)
I need 3% for Pete, 3% for Mary and 3 % for Steve
rfedorovAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
You can multiply the .03 (3%) times the count of each of the Name values.  However, you can't use the Top N in a single query to get the results you seek.
0
rfedorovAuthor Commented:
thank you for your respond, bit  i did not get that
0
aikimarkCommented:
i did not get that

Please clarify what you mean.  What is the "that" you aren't getting?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
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]);

Open in new window

/gustav
0
rfedorovAuthor Commented:
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
0
Gustav BrockCIOCommented:
Well, one name may represent 3% ...

/gustav
0
aikimarkCommented:
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.
0
rfedorovAuthor Commented:
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...
0
aikimarkCommented:
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

Open in new window

You must create the Top3Pct table.
0
Gustav BrockCIOCommented:
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

Open in new window

/gustav
0
rfedorovAuthor Commented:
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?
0
Gustav BrockCIOCommented:
I can't follow. Could you rephrase your comment, please?

/gustav
0
rfedorovAuthor Commented:
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
0
Gustav BrockCIOCommented:
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
0
rfedorovAuthor Commented:
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)
0
aikimarkCommented:
I posted VBA code.  It needs to go in a module.
0
Gustav BrockCIOCommented:
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
0
rfedorovAuthor Commented:
thank you very much

gustav: the problem is --it is not always, sometimes
aikimark: it is kind of complicated for me
0
Gustav BrockCIOCommented:
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

Open in new window

/gustav
0
aikimarkCommented:
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.
0
rfedorovAuthor Commented:
Thank you for your concern
The database included
RandomSelectionDB.accdb
0
aikimarkCommented:
Look at this updated version of your database.
RandomSelectionDB.accdb
0
rfedorovAuthor Commented:
thank you very much, i need some time to check and i will be back to you
0
Gustav BrockCIOCommented:
Thanks for the data. I can see, that my last query works right away:
SELECT 
    List.[Processor Long Name],
    List.Id
FROM
    tblHEER_QC_Selection As List
WHERE
    ID IN
        (SELECT TOP 3 PERCENT ID
        FROM tblHEER_QC_Selection As R
        WHERE R.Picked = False 
        AND
        R.[Processor Long Name] = List.[Processor Long Name]
        ORDER BY Rnd(-Timer()*R.[Id]))
Group By
    List.[Processor Long Name],
    List.ID

Open in new window

However, your issue is that Access SQL rounds up the 3 percent:

Processor Long Name  TotalID	FoundID	SelectID
Mary	                2	    0,06	    1
Pete	                162	    4,86	    5
Steve	                76	    2,28	    3

Open in new window

Thus, you get this example output:
Processor Long Name	Id
Mary	2
Pete	73
Pete	90
Pete	107
Pete	192
Pete	226
Steve	141
Steve	175
Steve	209

Open in new window

You can adjust the 3% down to 2% but Mary will still come out with 1 item.

If you wish an exact 3% count, you will have to use VBA to obtain the counts and run individual queries.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rfedorovAuthor Commented:
thank you very much
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.