?
Solved

Query for first, second, third, etc in Access.

Posted on 2004-11-20
5
Medium Priority
?
995 Views
Last Modified: 2012-06-21
Access has built-in query functions in the "Total" drop-down box for First & Last and Min & Max.  Any easy way to add the ability to ask for first, second, third, etc?  Thanks.
0
Comment
Question by:yosemitee
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:pique_tech
ID: 12636704
Not really easy and certainly not very dynamic.

Any approach to this kind of problem will involve multiple nested queries in the form of (pseudo-code, meant to convey concept, not meant to execute)
SELECT TOP 1 FROM
(SELECT TOP X FROM ... WHERE ... ORDER BY ... DESC)
where the X is the item you want, and derived tables based on these nested subqueries as you try to join your results back to the original tables to get the results you expect.

Access handles one subquery OK, but really only performs marginally well with derived tables.  I can't imagine how much <sarcasm> FUN </sarcasm> one might have trying to combine both.

I've seen folks here who can get Access 97 to hop in counter-clockwise circles on its left foot, so I wouldn't be surprised if others have specific clever solutions I haven't thought of.  I'm just offering my high-level gut-feel response to your general question--I'm sure willing to be wrong.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 2000 total points
ID: 12636715
Hi yosemitee

Add a counter field to you sql then set the where condition to filter primary key field on a parameter input.
The following works on a table called ServiceCalls, you would need to modify to suit your table name and field names.

PARAMETERS pWhichRecord Short;
SELECT T1.ID, (Select  Count(ID) from ServiceCalls where ID <= T1.ID) AS [counter]
FROM ServiceCalls AS T1
WHERE ((((Select  Count(ID) from ServiceCalls where ID <= T1.ID))=[pWhichRecord]));

Alan
0
 
LVL 3

Expert Comment

by:randyd
ID: 12638170
Cardinal position in an ordered list is returned by the pseudocolumn ROWNUM.

you can wrap your table sorta like this:

SELECT * FROM
(
SELECT ROWNUM AS the_row_num, t.*
FROM TABLE t
)

then apply a WHERE clause to find MIN or MAX rownum, or even a specific row as in

WHERE the_row_num = 7;

hth
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12638198
If I try RandyD's suggestion IN ACCESS, I get prompted for the ROWNUM parameter.  Maybe his suggestion is oriented to SQL?
0
 
LVL 3

Expert Comment

by:randyd
ID: 12638349
ooh shoot - i was giving an ORACLE Answer...  got into the wrong folder
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

809 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