Subquery (maybe) Problem Trying to Get a Particular Record from a Group of Records

[Don]

This is an expansion of my original post (10/2/2005 - "Returning First (or
just one) Record").

In trying to work through what might be a solution, it dawned on me that
maybe I did not explain the question well enough. To work through the
problem, I developed a similar table construct. Hopefully someone out there
can give me a few pointers in the right direction.

"Original" table:
StudentID LastName FirstName MaleFemale Age
1 Doe Jane No 10
2 Doe Johnny Yes 9
3 Smith Suzy No 7
4 Jones Rick Yes 9
5 Doe Buck Yes 8
6 Doe Betty No 7
7 Bopp Betty No 8
8 Bear Yogi Yes 8
9 Smith Sammy Yes 8


My objective is to develop a query which, in this example, will return all
records of one sex (M or F). If there is more than one with the same last
name (I am assuming they are from the family so they are siblings) I only
want the name of the oldest sibling. So, running this "mind" query on the
above table selecting females, the result would be:

1 Doe Jane No 10
3 Smith Suzy No 7
7 Bopp Betty No 8

I have tried a variety of forms of subqueries, unions, DISTINCT and TOP, but
either end up with a single record, or all the records.

There has got to be a relatively simple solution to this, but it is truly
escaping me. (Yes, I am a relative newbie!) Another example of this type
problem would be finding the most expensive item in an order for a variety
of orders.

Any assistance will be greatly appreciated!

Thanks!

Don

 

[SusanV]

Use Max on the age column:

SELECT Table1.StudentID, Table1.LastName, Table1.FirstName,
Table1.MaleFemale, Max(Table1.Age) AS MaxOfAge
FROM Table1
GROUP BY Table1.StudentID, Table1.LastName, Table1.FirstName,
Table1.MaleFemale
HAVING (((Table1.MaleFemale)=False));

 

[peregenem]

Use Max on the age column:

SELECT Table1.StudentID, Table1.LastName, Table1.FirstName,
Table1.MaleFemale, Max(Table1.Age) AS MaxOfAge
FROM Table1
GROUP BY Table1.StudentID, Table1.LastName, Table1.FirstName,
Table1.MaleFemale
HAVING (((Table1.MaleFemale)=False));

Add Jane's older sister, Jo:

INSERT INTO Table1
VALUES (101, 'Doe', 'Jo', 0, 12)

and your query returns both Jane and Jo. You can't group by StudentID
(the OP said LastName is the key).

Here's a suggested fix:

SELECT T1.*
FROM Table1 AS T1
INNER JOIN (
SELECT LastName, Max(Age) AS MaxOfAge
FROM Table1
WHERE MaleFemale = 0
GROUP BY LastName) AS DT1
ON T1.LastName = DT1.LastName
AND T1.Age = DT1.MaxOfAge

 

[Michel Walsh]

Hi,


A possible solution:


SELECT LAST(a.StudentID), a.LastName, LAST(a.FirstName), a.MaleFemale, a.Age
FROM (myTable As a INNER JOIN myTable As b
ON a.LastName=b.LastName AND a.MaleFemale=b.MaleFemale)
WHERE a.MaleFemale = false
GROUP BY a.Lastname, a.MaleFemale, a.Age
HAVING a.Age=MAX(b.Age)


Note that assumed there is just one record, per LastName, per MaleFemale,
per Age. As example, if there is a Mary Doe 10 years old,... you will got
just either Mary, either just Jane. If you want BOTH, in this circumstance,
then use a subquery (or a solution based on "ranking").



Hoping it may help,
Vanderghast, Access MVP

 

[Don]

SusanV,

Well, I must be missing something! I edited your SQL to use my table name:

SELECT tblStudents.StudentID, tblStudents.LastName, tblStudents.FirstName,
tblStudents.MaleFemale, Max( tblStudents.Age) AS MaxOfAge
FROM tblStudents
GROUP BY tblStudents.StudentID, tblStudents.LastName,
tblStudents.FirstName,
tblStudents.MaleFemale
HAVING ((( tblStudents.MaleFemale)=False));

And the query result is empty. I had actually tried the MAX function in my
"experimentation", but it looks like it must be combined with the GROUP BY
clause (or maybe HAVING?). Need to read up on that more.

Anyway, any ideas why the query would return nothing?

Thanks for the help!!

Don

 

[SusanV]

Hi Don,

Sorry, but my solution was faulty - check the later responses for a better
solution.

Hope I didn't confuse you more!

Susan

 

[Don]

SusanV,

Actually a BIG OOOOOOps on my part! I had selected YES/NO for MaleFemale.
Because of that, there were indeed no records because the MaleFemale field
only has YES and NO.

I am also reviewing Michel's response too!

Oh so much to absorb!

Thanks!

Don

 

[SusanV]

Don,

Just for future reference, if you have a Yes/No field, you can query it
multiple ways - they all mean the same to Access:
Checked = True = Yes = -1
Unchecked = False = No = 0

;-D

 

[Don]

Vanderghast,

Between you and SusanV, you have set me in the right direction!

Thanks!

Don