Selecting Households (Avoiding duplicates)

[Fdisk]

This is another newbie question from your friend Fdisk...

I have 2 tables with the exact same records. On has the addresses separated by house number, st, direction, type, etc. The other has the address in full.

I want to select * but only households, not individuals.

If I do SELECT DISTINCT Address FROM Table2 I get 296 distinct records, that's great. However, if I do

SELECT DISTINCT Address, PrimaryKey FROM Table2

I get all 400 records because the PrimaryKey field is full of uniques, no repeated values. How can I make a query to select only one person per household?

What I'd like to do is a query where I select the distinct addresses and only 1 of the many PrimaryKeys that might be in the same address so I can link it to Table1 and get the rest of the info from it.

Thanks in advance for the help.

 

[IanH]

It depends on which version of the record you wish to pull out. I would suggest that you might want the last / latest one that was added.

In that case a simple

SELECT Address, MAX(PrimaryKey)
FROM Table2
GROUP BY Address

would give you each unique version of the address field with the latest update to the PrimaryKey field.

Hope that helps

Ian

 

[Fdisk]

Thanks for the quick reply, but this is not exactly what I'm looking for. The query that you suggest would give me the Address field only. That can achieve by a SELECT DISTINCT statement as well.

I want to select FirstName, LastName, Address from a table, but in the case that 3 people live in the same house I Access to return to me only one of the records and not all 3 of them.

i.e.

John Smith 123 East Drive
Sarah Smith 123 East Drive
Jimmy Jones 123 East Drive
Sam Kent 245 West Drive
Maggy Simmons 245 West Drive

I want to do a query that will only return:

John Smith 123 East Drive
Sam Kent 245 West Drive

P.S. Sorry it took me so long to reply, I just spent almost a month at a place with no internet connection.

 

[IanH]

Apologies, I misunderstood slightly, but nevertheless you can still build on the query I gave you, using it as a subquery. This would look like ...

SELECT Table2.*
FROM Table2 INNER JOIN
(SELECT Address, MAX(PrimaryKey) AS MaxPrimaryKey
FROM Table2
GROUP BY Address) AS SubQry1 ON Table2.PrimaryKey = SubQry1.MaxPrimaryKey;

You can vary this and use, for example the MIN function to get the first record added to the table, or use the MIN or MAX on the name of the person rather than the primary key - if you were looking for a female or male head of household and had additional details such as age and sex you could throw that into your subquery as well. Not the prettiest way of writing this - it could be coded a little better - but if you haven't got too many records, and want something that is functional then this will do the job

Regards

Ian

 

[Fdisk]

Once again thanks for the quick reply, I'll try this as soon as I get a chance and let you know if it works