MS Access Top N Values Show in Table

sudarshank2

New Member
Joined
Nov 1, 2015
Messages
12
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_CodePrd_DescCat_CodeCat_DescQ1_SalesQ2_SalesQ3_SalesQ4_Sales
111ABC11A12128734
222DEF22B43565656
333GHI33C45346775
444JKL44D56675478

<colgroup><col span="8"></colgroup><tbody>
</tbody>

My output data should be: (Top 2 Values in Each Column)
Prd_CodePrd_DescCat_CodeCat_DescQ1_SalesQ2_SalesQ3_SalesQ4_Sales
111ABC11A 87
222DEF22B 56
333GHI33C45 6775
444JKL44D5667 78

<colgroup><col span="8"></colgroup><tbody>
</tbody>

SQL Query or VBA both are fine for me.

Can somebody please help.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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 [COLOR=#ff0000]MyTable
[/COLOR]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 [COLOR=#ff0000]MyTable[/COLOR]
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 [COLOR=#ff0000]MyTable
[/COLOR]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 [COLOR=#ff0000]MyTable
[/COLOR]ORDER BY Q4_Sales DESC) [Q4])  AS MyUnion
GROUP BY Prd_code,Prd_desc, Cat_code, Cat_desc
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top