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

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.
yosemiteeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pique_techCommented:
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
Alan WarrenApplications DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
randydCommented:
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
pique_techCommented:
If I try RandyD's suggestion IN ACCESS, I get prompted for the ROWNUM parameter.  Maybe his suggestion is oriented to SQL?
0
randydCommented:
ooh shoot - i was giving an ORACLE Answer...  got into the wrong folder
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.