return only one household member for each household

[machumpion]

Let's say I have a table featuring two fields, [household] and [member]. Each household can have multiple members. Therefore the household field can have duplicates.

I want a query that only returns one record per household, but I also want to return 1 member only (any member) for each.

How can I make this query?
Thanks!

 

[sneuberg]

I believe the easiest way to do that would be with a aggregate query. You can create a query like this in Query design by clicking on the sigma in the ribbon. You would group by the household and choose first or last for the member. The SQL for this query would be:

SELECT Table1.household, First(Table1.member) AS FirstOfmember
FROM Table1
GROUP BY Table1.household;

and is demonstrated in the attached database. Note that this returns households with no members. If you don't want that you can add a where condition for the member field of Not Is Null