MS Access Top N Values Show in Table

[sudarshank2]

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:
[TABLE="width: 592"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]Prd_Code[/TD]
[TD]Prd_Desc[/TD]
[TD]Cat_Code[/TD]
[TD]Cat_Desc[/TD]
[TD]Q1_Sales[/TD]
[TD]Q2_Sales[/TD]
[TD]Q3_Sales[/TD]
[TD]Q4_Sales[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]ABC[/TD]
[TD]11[/TD]
[TD]A[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]87[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]DEF[/TD]
[TD]22[/TD]
[TD]B[/TD]
[TD]43[/TD]
[TD]56[/TD]
[TD]56[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]GHI[/TD]
[TD]33[/TD]
[TD]C[/TD]
[TD]45[/TD]
[TD]34[/TD]
[TD]67[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]JKL[/TD]
[TD]44[/TD]
[TD]D[/TD]
[TD]56[/TD]
[TD]67[/TD]
[TD]54[/TD]
[TD]78[/TD]
[/TR]
</tbody>[/TABLE]

My output data should be: (Top 2 Values in Each Column)
[TABLE="width: 592"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD]Prd_Code[/TD]
[TD]Prd_Desc[/TD]
[TD]Cat_Code[/TD]
[TD]Cat_Desc[/TD]
[TD]Q1_Sales[/TD]
[TD]Q2_Sales[/TD]
[TD]Q3_Sales[/TD]
[TD]Q4_Sales[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]ABC[/TD]
[TD]11[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]87[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]DEF[/TD]
[TD]22[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD]56[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]333[/TD]
[TD]GHI[/TD]
[TD]33[/TD]
[TD]C[/TD]
[TD]45[/TD]
[TD] [/TD]
[TD]67[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]444[/TD]
[TD]JKL[/TD]
[TD]44[/TD]
[TD]D[/TD]
[TD]56[/TD]
[TD]67[/TD]
[TD] [/TD]
[TD]78[/TD]
[/TR]
</tbody>[/TABLE]

SQL Query or VBA both are fine for me.

Can somebody please help.

 

[stumac]

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

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

 

[sudarshank2]

Thank you so much... I made little modification