Solved

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

Posted on 2006-10-29
6
647 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 34

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 34

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 34

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 34

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

830 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