Problem with "Top N Value" Query

[JeffS66]

I followed/modified the "Criteria" in Microsoft KB Q153747 but do not
get the desired results. Instead of returning the Top-10 per Group
(LOC) I only get the Top-10 across all groups ... the same as if I use
the Top Values box in the query design tool bar.
Here is my SQL ... Any assistance is greatly appreciated!

SELECT [tbl_Short Lines by LOC].LOC, [tbl_Short Lines by LOC].PART,
[tbl_Short Lines by LOC].ITMDSG, [tbl_Short Lines by LOC].DSC,
[tbl_Short Lines by LOC].MPRODCAT_DSC, [tbl_Short Lines by LOC].[SHORT
LINES]
FROM [tbl_Short Lines by LOC]
WHERE ((([tbl_Short Lines by LOC].[SHORT LINES]) In (Select Top 10
[SHORT LINES] From [tbl_Short Lines by LOC] Where [LOC]=[tbl_Short
Lines by LOC].[LOC] Order By [SHORT LINES] Desc)))
ORDER BY [tbl_Short Lines by LOC].LOC, [tbl_Short Lines by LOC].[SHORT
LINES] DESC;

Thank you in advance.
Jeff

 

[Guest]

Try

SELECT T1.LOC, T1.PART,T1.ITMDSG, T1.DSC,T1.MPRODCAT_DSC, T1.[SHORT
LINES]
FROM [tbl_Short Lines by LOC] As T1
WHERE T1.[SHORT LINES]) In (Select Top 10
T2.[SHORT LINES] From [tbl_Short Lines by LOC] As T2 Where T2.[LOC]=T1.[LOC]
Order By T2.[SHORT LINES] Desc)
ORDER BY T1.LOC, T1.[SHORTLINES] DESC

 

[JeffS66]

Thank you!

I now have another problem:
I'm getting more than 10 records per group (LOC) where there is a tie
or multiples of the same "SHORT LINES" quantity. I just want to see 10
records per group (LOC) even if all 10 have the same "SHORT LINES"
quantity.
Ex:

LOC PART SHORT LINES
1 A 6
1 B 5
1 C 4
1 D 3
1 E 2
1 F 2
1 G 1
1 H 1
1 I 1
1 J 1
1 K 1
1 L 1
1 M 1
1 N 1
1 O 1

Thank you.
Jeff

 

[Michel Walsh]

Hi,


Add the primary key in the inner ORDER BY, such as:

SELECT T1.LOC, T1.PART,T1.ITMDSG, T1.DSC,T1.MPRODCAT_DSC, T1.[SHORT
LINES]
FROM [tbl_Short Lines by LOC] As T1
WHERE T1.[SHORT LINES]) In (Select Top 10
T2.[SHORT LINES] From [tbl_Short Lines by LOC] As T2 Where T2.[LOC]=T1.[LOC]
Order By T2.[SHORT LINES], T2.PrimaryKey Desc)
ORDER BY T1.LOC, T1.[SHORTLINES] DESC





Hoping it may help,
Vanderghast, Access MVP

 

[JeffS66]

That worked perfectly. Thank you.