Smallest in group

[pvanryzin]

I need to find the youngest kid of each last name

Anderson Susie 19
Anderson Bob 12
Anderson Lenny 15
Billings George 8
Cooper Sandy 10
Cooper Andrew 12
Donzi Alicia 8
Donzi Rachel 12

I need to get back

Anderson Bob 12
Billings George 8
Cooper Sandy 10
Donzi Alicia 8

I thought this would be trivial but I just can't seem to crack this one.

Thanks in advance


Pierette

[dcrake]

Your first issue is to split the first and last names into two seperate columns/fields then group by the surname and use minimum on the age field/column.

To get the first word in the string you can do the following

Surname:Trim(Left([FullName],InStr([Full Name]," ")))

David

[pvanryzin]

The names and ages are already in separate fields. I was trying to solve the problem exactly as you described (group by lastname, min of age). But when I set up the query to allow grouping, it insists on grouping all of the fields and then doesn't work. If I remove the Group By for those fields I don't want to group, I get the error message "you tried to execute a query in which FirstName is not an aggregate field" (or some such thing).

So in the query I removed all fields except LastName and Age and grouped by LastName and did a min on Age and it did the right thing. Problem is, I don't have any of the additional data in the resulting query that I would like to have (FirstName, Team, ContactInfo, etc). I can't join it against the main table to get this info because LastName isn't a unique key ..... although I suppose LastName and age are unique but geeez - what an ugly way to do it - isn't there anything more straighforward? Like you , I thought I would be able to "carry along" the additional fields in the query without having them effect the result. Have you run into this delimna before?

[I-am-me]

Do another join query that joins the new resulting query and the original table. In the design view, link last name to last name and age to age, then double click on the link between them and choose the option "only values in table 2 but not table 1" or something of that sort - the option that says you only want the last names of your second table.