Access Access SQL help

[marsbar1]

Please, please, please can anyone help.
I have a database of students and I need a query that will return the oldest child if more than one child lives at an address.

Thank you

 

[chaver]

Hi

You need to create a grouped query. I typed a full reply to you but somehow the message was lost - I am sending you this quick reply as a test - if it works OK I will give you more details.

 

[chaver]

OK it worked this time:

Create a new query on the table holding the data. Select the relevant fields e.g. Address (this must be a unique Text field that identifies the address Note: it can't be a Memo field), the Age of the Child and the child's name.

If you run this query it will show you all the children at all the addresses. Now change the query to a CrossTab Query. That will show a new line under each field showing 'Grouped'. Now change the query back to a Select Query and the line showing 'Grouped' will still be there. Go to the Age field and click on the drop down next to Grouped. Select Max. If you run the query now it will only show the oldest child at each address.

Hope that helps