MS ACCESS, Query to select first (n) records in category.

SELECT Top 2 Table1.Territory, Table1.Segment FROM Table1 WHERE (((Table1.Territory)="a")) Union
SELECT Top 2 Table1.Territory, Table1.Segment FROM Table1 WHERE (((Table1.Territory)="b"));

Here I have assumed you have two Territories and Top n is hard coded.  This can be changed where the user is prompted once to enter a value.  Also, as you can see, with this solution, we need to know what Territories exist.

If the number or the title of Territories are not known, then SQL needs to be build using VBA and via QueryDef coding stored in a query (say qTerritory) before running:

DoCmd.OpenQuery "qTerritory"

Mike

GO07470,

Using the solution from LPurvis, this is how you can request for Top n dynamically:

1. Save the solution as qTerritory.


Dim strSql As String
Dim varVal As Variant
Dim intTop As Integer

varVal = InputBox("Enter Top Value: ")

If IsNumeric(varVal) Then
   If CInt(varVal/2)*2 <> varVal Then
      MsgBox "Please enter an integer..."
      Exit Sub
   End If
   intTop = varVal
End If

strSql = "SELECT T1.PKID, T1.Territory, T1.Segment, T1.Units, T1.Decile FROM tblTerritories T1" & _
           " WHERE T1.PKID In (SELECT TOP " & intTop  & " T2.PKID FROM tblTerritories T2 WHERE T1.Territory = T2.Territory" & _
           " ORDER BY T2.Territory, T2.Segment, T2.Units DESC, T2.Decile DESC) ORDER BY" & _
           " T1.Territory, T1.Segment, T1.Units DESC, T1.Decile DESC"

CurrentDB.QueryDefs("qTerritory").SQL = strSql
DoCmd.OpenQuery "qTerritory"


You could include the above code in on click event of button.

Mike

A minor correction...

If IsNumeric(varVal) Then
   If CInt(varVal/2)*2 <> varVal Then
      MsgBox "Please enter an integer..."
      Exit Sub
   End If
   intTop = varVal
Else
      MsgBox "Please enter an integer..."
      Exit Sub
End If

This portion could be improved a bit.  

Hi everyone,
I need to take this one step at a time,
eghtebas,
From my data Table, I built a Query (Query1) that puts the data in the desired Sort order as I mentioned in my posed question. If I substitute that into your first solution I have:

SELECT Top 2 [Query1].Terr, [Query1].Segment FROM [Query1] WHERE ((([Query1].Terr)="a")) Union
SELECT Top 2 [Query1].Terr, [Query1].Segment FROM [Query1] WHERE ((([Query1].Terr)="b"));
 
For me to hard code the number of rows desired ( 10, for example) and the total number of unique Territories in the Query  (as in 835, for a particular month)
is not a problem. Do I understand the solution then becomes:

SELECT Top 10 [Query1].Terr, [Query1].Segment FROM [Query1] WHERE ((([Query1].Terr)="835")) Union
SELECT Top 10 [Query1].Terr, [Query1].Segment FROM [Query1] WHERE ((([Query1].Terr)="835"));  ?