Use max value with random numbers

Hi I have following query which helped to generate top 5 random accounts for each name.

Now, I need this task to be more difficult.  I need to limit the max value for each name as 4.
Would you please use query only is possible. Please see an attached database for reference.

Detailed explanation:

1. "Table 1" is the main table;
2. "Query1-generate random numbers" is the table with randomly generated accounts for each name. Sort in Query 1 is ensures that numbers are randomly selected and will be different during each run.
3. Table 2 is mostly needed for validation purposes.
4. As you can see that some name reflects 3 accounts, some 5, some 7 and some 8 accounts.
4.1 For each name I need to use same query "Top5-Query1" to randomly select top 4 numbers and limit max value to 4 for each name. Note: in  real database the query "top5-Query1" reflects top value as percentage. Therefore I need to limit max value and it can be changed in future.
DB2.accdb
maximyshkaAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
Am I missing something?

Try Just changing "TOP 5" in your query's SQL to:  TOP 4
0
maximyshkaAuthor Commented:
Sorry, for confusion

I adjusted database and step 4.1

4.1 For each name I need to use same query "Top5-Query1" to randomly select top 60 Percent and limit max value to 4 for each name.  

This is the query:

SELECT table2.[Account Number], table2.[field name], table2.[Account Name]
FROM table2
WHERE (((table2.[Account Number]) In (SELECT TOP 60 PERCENT
      T.[Account Number] AS AN
    FROM
      Table2 As T
    WHERE
      (T.[Field Name]=Table2.[Field Name])
    ORDER BY
      [Rnd Generator])))
ORDER BY table2.[field name];

This is the result:

Account Number      field name      Account Name
Account Number      field name      Account Name
10      alex      w
7      alex      g
3      mike      c
8      mike      e
6      mike      f
21      mike      t
20      mike      s
2      nick      b
19      nick      r
14      nick      k
12      nick      e
1      nick      a
16      peter      m
4      peter      d
5      peter      e

Suggested outcome (to have max 4 records for Mike and Nick):

Account Number         field name         Account Name
10        alex           w
7        alex            g
3        mike      c
8        mike      e
6      mike      f
21      mike      t
2       nick       b
19      nick              r
14      nick             k
12      nick        e
16      peter      m
4      peter      d
5      peter      e

Note: this is needed for the real database.  I provided you a demo to ask the question.
Please see an attached database
DB2.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
OK, then I would suggest that you click the "request attention" link and ask that the SQL syntax Topic Area be added to this Q...
0
mbizupCommented:
That's basically a TOP N per Group query as described by Allen Browne here:
http://allenbrowne.com/subquery-01.html

The only difference is that you are basing it on a query rather than a table.  The syntax is basically the same as that posted in the article, except you have to substitue the entire query for the Orders table:

SELECT q.[Account Number], q.[field name], q.[Account Name]
FROM
(SELECT table2.[Account Number], table2.[field name], table2.[Account Name]
FROM table2
WHERE (((table2.[Account Number]) In (SELECT TOP 60 PERCENT
      T.[Account Number] AS AN
    FROM
      Table2 As T
    WHERE
      (T.[Field Name]=Table2.[Field Name])
    ORDER BY
      [Rnd Generator])))
ORDER BY table2.[field name]) q
WHERE q.[Account Number]
IN 
(SELECT TOP 4 [Account Number] FROM
(SELECT table2.[Account Number], table2.[field name], table2.[Account Name]
FROM table2
WHERE (((table2.[Account Number]) In (SELECT TOP 60 PERCENT
      T.[Account Number] AS AN
    FROM
      Table2 As T
    WHERE
      (T.[Field Name]=Table2.[Field Name])
    ORDER BY
      [Rnd Generator])))
ORDER BY table2.[field name]) q2
WHERE q.[field name] = q2.[field name])

Open in new window

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
maximyshkaAuthor Commented:
Thanks a lot.  Great help!!!
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.