Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 674
  • Last Modified:

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

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
0
GO07470
Asked:
GO07470
  • 4
2 Solutions
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mike EghtebasDatabase and Application DeveloperCommented:
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.  
0
 
GO07470Author 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"));  ?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now