Little challenging query

  • Thread starter Thread starter Dornenhexe
  • Start date Start date
D

Dornenhexe

Hi,

I have been working on this little query for the last week and I just can't
get the solution.
I just hope you guys are smarter than I...

Anyway the question is this:

I got one table: People(name, gender, ssn)
The table is orderd by the ssn which is also the key.

What I need to know: Who is the 3rd male in People after Howie?

Thanks soooooo much in advance.

Dornenhexe
 
Access stores data in tables in whatever order it wishes (perhaps an
exaggeration, but you get the idea).

When you say you want the "3rd Male after Howie", you are assuming there is
some rhyme/reason/order to the data. Any order exists ONLY if YOU define
the order.

What order are YOU using?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Dornenhexe said:
Hi,

I have been working on this little query for the last week and I just can't
get the solution.
I just hope you guys are smarter than I...

Anyway the question is this:

I got one table: People(name, gender, ssn)
The table is orderd by the ssn which is also the key.

What I need to know: Who is the 3rd male in People after Howie?

Thanks soooooo much in advance.

Dornenhexe
Click to expand...

If you mean be "3rd male after Howie" the third male with Name >
"Howie" then try the following:

SELECT TOP 3 People.Name, People.gender, People.ssn
FROM People
WHERE (((People.Name)>"Howie") AND ((People.gender)="m"))
ORDER BY People.Name DESC;

Example for table People:

Name gender ssn
Abel m 1
Berta f 2
Cecil m 3
Howie m 4
Frederick m 5
Gerhard m 6
Anna f 7
Katie f 8
Melanie f 9
Isidor m 10
Klaus m 11
Laura f 12
Martin m 13

the above query results in

Name gender ssn
Martin m 13
Klaus m 11
Isidor m 10

and the first record is your answer.

Likewise, if you mean "the third male after Howie with ssn > Howies
ssn, then try the following:

SELECT TOP 3 People.Name, People.gender, People.ssn
FROM People
WHERE (((People.gender)="m") AND
((People.ssn)>DLookUp("ssn","People","Name = 'Howie'")))
ORDER BY People.ssn;

This thime the output is

Name gender ssn
Frederick m 5
Gerhard m 6
Klaus m 11

and it is the *last* record that contains your answer.

I hope this gives you some ideas.

Greetings
Matthias Kläy
 
This answer goes into the right direction.

Matthias said:
Likewise, if you mean "the third male after Howie with ssn > Howies
ssn, then try the following:

SELECT TOP 3 People.Name, People.gender, People.ssn
FROM People
WHERE (((People.gender)="m") AND
((People.ssn)>DLookUp("ssn","People","Name = 'Howie'")))
ORDER BY People.ssn;

This thime the output is

Name gender ssn
Frederick m 5
Gerhard m 6
Klaus m 11

and it is the *last* record that contains your answer.
Click to expand...

However, my problem is that I'm suppose to output only the 3rd male after
Howie and not the two guys that are in between them.

Is there a function that only returns the content of row number 3?

Thanks,
Dornenhexe
 
Jeff said:
When you say you want the "3rd Male after Howie", you are assuming there is
some rhyme/reason/order to the data. Any order exists ONLY if YOU define
the order.

What order are YOU using?
Click to expand...

The list is orderd by the ssn: 1, 2, 3, 4, 5, .....

Dornenhexe
 
Dornenhexe said:
This answer goes into the right direction.



However, my problem is that I'm suppose to output only the 3rd male after
Howie and not the two guys that are in between them.

Is there a function that only returns the content of row number 3?

Thanks,
Dornenhexe
Click to expand...

Hmm... the simplest way I know is to save the first query with a name,
say "qry_Intermediate", then do in another query

SELECT TOP 1 Name, gender, ssn FROM qry_Intermediate
ORDER BY ssn DESC;

Greetings,
Matthias Kläy
 
That sounds almost too simple.

However, I don't know if my teacher would accept the answer in two different
queries.

On the side...
Is there also a function like: SELECT BOTTOM...?

Dornenhexe
 
Dornenhexe said:
That sounds almost too simple.

However, I don't know if my teacher would accept the answer in two different
queries.

On the side...
Is there also a function like: SELECT BOTTOM...?

Dornenhexe
Click to expand...

First of all, you DO use Microsoft Access, do you? There are many
variants of SQL out there with widely differing possibilities as to
how to solve one and the same problem.

SELECT BOTTOM as such does not exist in MS Access SQL; you would start
with

SELECT TOP 2 Name, gender, ssn FROM People
ORDER BY ssn DESC;

This would give you the highest and the second highest ssn (in this
order). If you would like to have them in the order "second highest",
"highest", then again, you would have to use two queries again; let us
name the first one qry_Bottom, then you would do in another query

SELECT Name, gender, ssn FROM qry_Bottom
ORDER BY ssn ASC;


If your teacher does not accept the solution with two queries, he is
very welcome to post his solution here, so we all can learn something
new from him.

Greetings
Matthias Kläy
 
Yes, I'm using Microsoft Access.

I will propose this solution to him and I'm curious about his answer.

Should he have a different one I will happily post his answer!

Thank you very much for your help!

Gute Nacht...
Dornenhexe
 
Dornenhexe said:
Yes, I'm using Microsoft Access.

I will propose this solution to him and I'm curious about his answer.

Should he have a different one I will happily post his answer!

Thank you very much for your help!

Gute Nacht...
Dornenhexe
Click to expand...

Bitte gern geschehen... :-)

Well there *may* be a solution in one query, but right now I have to
go to sleep, I am too tired. I will have another look at it tomorrow.

Matthias Kläy
 
Dornenhexe said:
Yes, I'm using Microsoft Access.

I will propose this solution to him and I'm curious about his answer.

Should he have a different one I will happily post his answer!

Thank you very much for your help!

Gute Nacht...
Dornenhexe
Click to expand...

Since I cannot sleep anyway, how do you like this:

SELECT Top 1 People.Name, People.gender, People.ssn
FROM People
Where People.ssn IN
(SELECT TOP 3 Temp.ssn
FROM People As Temp
WHERE (Temp.gender="m") AND
(Temp.ssn > DLookUp("ssn","People","Name = 'Howie'"))
Order By Temp.ssn asc)
ORDER BY People.ssn DESC;

I really dont like subqueries (seems to be some kind of allergy ...),
so I always forget about them.
I leave it to you as an exercise to explain
why this is working ... :-)

Matthias Kläy
 
Back
Top