Retreive same record

[ielmrani via AccessMonster.com]

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
Paul 345 Off2 2 Bellmore Ave
Paul 234 Off2 132 Hill St


thanks in advance
dean

 

[Guest]

Use two queries like this --
ielmrani_1 ---
SELECT ielmrani.Name, ielmrani.Office, Count(ielmrani.Office) AS CountOfOffice
FROM ielmrani
GROUP BY ielmrani.Name, ielmrani.Office
HAVING (((Count(ielmrani.Office))>1));

SELECT ielmrani.*
FROM ielmrani INNER JOIN ielmrani_1 ON (ielmrani.Office = ielmrani_1.Office)
AND (ielmrani.Name = ielmrani_1.Name);

 

[ielmrani via AccessMonster.com]

Worked. Thank you So much.

Ismail

Use two queries like this --
ielmrani_1 ---
SELECT ielmrani.Name, ielmrani.Office, Count(ielmrani.Office) AS CountOfOffice
FROM ielmrani
GROUP BY ielmrani.Name, ielmrani.Office
HAVING (((Count(ielmrani.Office))>1));

SELECT ielmrani.*
FROM ielmrani INNER JOIN ielmrani_1 ON (ielmrani.Office = ielmrani_1.Office)
AND (ielmrani.Name = ielmrani_1.Name);

Hi all,
I have the following table:

[quoted text clipped - 18 lines]

thanks in advance
dean

 

[John Spencer]

SELECT *
FROM YourTable
WHERE YourTable.Name In (
SELECT Temp.Name
FROM
(SELECT Distinct T2.Name, T2.Office
FROM YourTable as T2) as Temp
HAVING Count(Temp.Office) = 1) )

This should return only those Names where the Office is the same for all records
involving the individual. That is the Name always has the same Office value.

So if you added one more record for Kim
Kim 123 Off2 1 Walter St

Kim would no longer be in the results.

 

[ielmrani via AccessMonster.com]

Thanks John. Worked

SELECT *
FROM YourTable
WHERE YourTable.Name In (
SELECT Temp.Name
FROM
(SELECT Distinct T2.Name, T2.Office
FROM YourTable as T2) as Temp
HAVING Count(Temp.Office) = 1) )

This should return only those Names where the Office is the same for all records
involving the individual. That is the Name always has the same Office value.

So if you added one more record for Kim
Kim 123 Off2 1 Walter St

Kim would no longer be in the results.

Hi all,
I have the following table:

[quoted text clipped - 22 lines]

Message posted via AccessMonster.com