How to Retrieving same records

[Finedean]

Hi all,
I have the following table:

Name ID Office Address

Kim 123 Off1 125 Dekalb
Kim 123 Off1 17 New York Ave
John 234 Off2 12 Cram St
John 234 Off3 1 Walter St
John 234 off1 4 Broom Ave
Paul 345 Off2 2 Bellmore Ave
Paul 234 Off2 132 Hill St

I am trying to run a query to give me only records where office are equal. In this case I would get:
Kim 123 Off1 125 Dekalb
Kim 123 Off1 17 New York Ave

thanks in advance
dean

 

[Finedean]

Sorry I made a mistake:
the query should return:

Kim 123 Off1 125 Dekalb
Kim 123 Off1 17 New York Ave
Paul 345 Off2 2 Bellmore Ave
Paul 234 Off2 132 Hill St

because they both have the same office.
thanks.

 

[traingamer]

one way:

SELECT t1.[Name], t1.[ID],  t1.[Office], t1.[Office]
FROM yourtable t1 where t1.[Office] in (SELECT t2.[Office]
FROM yourtable t2
GROUP BY t2.[Office]
HAVING (((Count(t2.[Id]))>1)));

which would also return
John 234 off1 4 Broom Ave
because Access wouldn't care about the case.

This would return all rows where there is more than one row in an office.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Finedean]

Thank you.
Dean

 

[PHV]

SELECT DISTINCT A.Name, A.ID, A.Office, A.Address
FROM yourTable AS A INNER JOIN yourTable AS B
ON A.Name=B.Name AND A.ID=B.ID AND A.Office=B.Office AND A.Address<>B.Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886