>Reposted for clarity:
>
If you only want to list one child you may join the table with itself, e.g.
select HouseHold, padr(alltrim(Parent.fName) + iif(IsNull(Child.fName),"",", " + Child.fName),40) as Inhabitans, ...
from myTable Parent left join myTable Child on Parent.HouseHold = Child.HouseHold
and Parent.PK <> Child.PK
where Parent.Age = (select max(Age) as Age from myTable X where X.HouseHold = Parent.HouseHold)
That's just an idea, not tested.
>Hi,
>
>Can I do the following with a select all type statment"
>
>I have a table that looks something like this:
>
>
>r# household Parent house# ages
>1 Smiths Robert 27 42
>2 Bobby 27 4
>3 Barnes Mary 74 32
>4 Margy 74 2
>etc.
>
>
>Can I use an SQL statement to generate a new table that looks anthing like this?
>
>
>r# household inhabitants house# ages
>1 Smiths Robert, Bobby 27 42,4
>2 Barnes Mary, Margy 74 32,2
>etc.
>
>
>Would it make a difference if the starting out table looked like this?
>
>
>r# household Parent house# ages
>1 Smiths Robert 27 42
>2 Smiths Bobby 27 4
>3 Barnes Mary 74 32
>4 Barnes Margy 74 2
>etc.
>
>
>Thanks very much,
>Steve
If it's not broken, fix it until it is.
My Blog