We help IT Professionals succeed at work.

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

GO07470
GO07470 asked
on
Medium Priority
684 Views
Last Modified: 2008-02-01
Hello,
In MS ACCESS 2002. I have one Table with 10 Fields, 4 are sorted in this order: Territory (ascending), Segment (ascending), Units (descending), and Decile (descending). Running this query I get the data in the correct order for each Territory , throughout the Table. There are 800 to 900 Territories. Now "all" I need to do is Select the first (n) (2, 23, or 56, for example) Rows from each Territory to display in the Query.
Thanks,
GO07470
Comment
Watch Question

Mike EghtebasDatabase and Application Developer

Commented:
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
Database Developer
CERTIFIED EXPERT
Commented:
800 to 900 territories?  ouch.
One SQL statement would be ideal - but you can't adaptively (i.e. with a parameter) specify the amount to appear in a TOP statement.
So you'd either have to decide what this query is designed to retrieve - or alter the SQL of it just prior to any execution to insert the appropriate integer into the statement.
This method depends on there being a unique Primary key field present too.  (Which you should *always* have).
Suppose it were called PKID.
Give this a try...

SELECT
  T1.PKID, T1.Territory, T1.Segment, T1.Units, T1.Decile
FROM
  tblTerritories T1
WHERE
   T1.PKID In (SELECT TOP 2 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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike EghtebasDatabase and Application Developer

Commented:
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
Mike EghtebasDatabase and Application Developer

Commented:
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.  

Author

Commented:
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"));  ?
Mike EghtebasDatabase and Application Developer
Commented:
GO07470,

Please use the solution from LPurvis.  And, because you need to change n in Top n with each use, then take a look at my later post to do so.

Mike
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.