MS Access Top N Values Show in Table
Results 1 to 3 of 3

Thread: MS Access Top N Values Show in Table

  1. #1
    New Member
    Join Date
    Nov 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post MS Access Top N Values Show in Table

    Hi,

    I have a table in Access which contains sales total. I need only Top 2 Values in each column.

    My input data is below:
    Prd_Code Prd_Desc Cat_Code Cat_Desc Q1_Sales Q2_Sales Q3_Sales Q4_Sales
    111 ABC 11 A 12 12 87 34
    222 DEF 22 B 43 56 56 56
    333 GHI 33 C 45 34 67 75
    444 JKL 44 D 56 67 54 78

    My output data should be: (Top 2 Values in Each Column)
    Prd_Code Prd_Desc Cat_Code Cat_Desc Q1_Sales Q2_Sales Q3_Sales Q4_Sales
    111 ABC 11 A 87
    222 DEF 22 B 56
    333 GHI 33 C 45 67 75
    444 JKL 44 D 56 67 78

    SQL Query or VBA both are fine for me.

    Can somebody please help.

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    434
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: MS Access Top N Values Show in Table

    One way using SQL would be to combine 4 Select top N queries then group them:

    Code:
    SELECT Prd_code, Prd_desc, Cat_code, Cat_desc, MAX(Q1_Sales) AS xQ1_Sales, MAX(Q2_Sales) AS xQ2_Sales, MAX(Q3_Sales) AS xQ3_Sales, MAX(Q4_Sales) AS xQ4_Sales
    FROM 
    (Select * from (SELECT TOP 2 Prd_code,Prd_desc, Cat_code, Cat_desc, Q1_Sales, Null AS Q2_Sales, Null AS Q3_Sales, Null AS Q4_Sales
    FROM MyTable
    ORDER BY Q1_Sales DESC) [Q1]
    UNION ALL
    Select * from (SELECT TOP 2 Prd_code,Prd_desc, Cat_code, Cat_desc,Null AS Q1_Sales, Q2_Sales, Null AS Q3_Sales, Null AS Q4_Sales
    FROM MyTable
    ORDER BY Q2_Sales DESC) [Q2]
    UNION ALL
    Select * from (SELECT TOP 2 Prd_code,Prd_desc, Cat_code, Cat_desc,Null AS Q1_Sales, Null AS Q2_Sales,Q3_Sales, Null AS Q4_Sales
    FROM MyTable
    ORDER BY Q3_Sales DESC) [Q3]
    UNION ALL
    Select * from (SELECT TOP 2 Prd_code,Prd_desc, Cat_code, Cat_desc,Null AS Q1_Sales, Null AS Q2_Sales,Null AS Q3_Sales, Q4_Sales
    FROM MyTable
    ORDER BY Q4_Sales DESC) [Q4])  AS MyUnion
    GROUP BY Prd_code,Prd_desc, Cat_code, Cat_desc
    Last edited by stumac; Aug 20th, 2019 at 04:46 AM.

  3. #3
    New Member
    Join Date
    Nov 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: MS Access Top N Values Show in Table

    Thank you so much... I made little modification

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •