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.

Not enough info. What's if, say, 3 students share the same birthday? Which one do you choose?

Sgt.Sausage
Monday, October 29, 2007

Deleting …Approving …

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 StudentsGROUP 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] fromCLASSES c join CLASSID_OLDEST_DOB cdobon c.class_id = cdob.class_idjoin STUDENTS s oncdob.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.