Solved

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

Posted on 2006-10-29
6
641 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
0
Comment
Question by:GO07470
  • 4
6 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 17830852
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
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 400 total points
ID: 17830955
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
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 17831047
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 17831059
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
 

Author Comment

by:GO07470
ID: 17832904
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
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
ID: 17834276
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question