The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Simple Access Query

I'm sure there is an easy way to do this, but I can't think of it without resorting to code. I've made it into a simple example to try and explain it. (I'm stuck with Access 97 if it makes any difference!)

What I want to do is create a query that will give me a dataset containing each class and the DoB of the oldest student in that class.

Class        Oldest Student  Oldest DoB
"Year 1"    Fred            11-4-1980
"Year 2"    Sally          12-12-1979
etc

Class Table
-----------
class_id
class_name

Student Table
-------------
student_id
student_name
student_dob
class_id
IanH Send private email
Monday, October 29, 2007
 
 
Not enough info. What's if, say, 3 students share the same birthday? Which one do you choose?
Sgt.Sausage
Monday, October 29, 2007
 
 
There are certainly ways to do it without code.  Not sure if this is best way, but it's relatively simple to do making two queries, one that is used as the basis for the second one.

First query gets list of class_id's and earliest dob for each.  Call it something like "CLASSID_OLDEST_DOB". Easy to do visually in Access, SQL might look something like this:
SELECT class_id, max(student_dob)
FROM Students
GROUP BY class_id;

Now make a second query that uses the first query (CLASSID_OLDEST_DOB) and links it to class table and student table.  Again, simple to do visually in Access and SQL might look like this:
SELECT c.classname as Class, s.student_name as [Oldest Student], s.student_dob as [Oldest DOB] from
CLASSES c join CLASSID_OLDEST_DOB cdob
on c.class_id = cdob.class_id
join STUDENTS s on
cdob.student_dob = s.student_dob;

I think this, or something close it it, will give the result you wanted.  Only possible difference is that if two or more students share oldest dob in a class they will each be listed in a row of the result set.  As previous poster pointed out, you haven't specified desired treatment in this case.
Herbert Sitz Send private email
Monday, October 29, 2007
 
 
Whoops, I think you'd actually want first query to be this:

SELECT class_id, MIN(student_dob)
FROM Students
GROUP BY class_id;
Herbert Sitz Send private email
Monday, October 29, 2007
 
 
> What's if, say, 3 students share the same birthday?

This is only a simple example. In the real thing this won't happen.
IanH. Send private email
Monday, October 29, 2007
 
 
Just tried the example on the proper database and (so far) looks like it will do the job.

Herbert - thanks for your help. I owe you a pint!

Sgt. I owe you a half ;-)
IanH. Send private email
Tuesday, October 30, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz