Duplicate records ... sort of

[Sue Compelling]

Evening

I have a select query that extracts constituents who have a "status" of
closed and a "type" of coordinator.

I now want to only retrieve those records where there is more than one
Coordinator per Region. Is this possible?

My code is ...

SELECT ([firstname] & " " & [lastname]) AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblRegions.Region
FROM tblRegions INNER JOIN tblContacts ON tblRegions.RegionID =
tblContacts.RegionFK
WHERE (((tblContacts.ContactStatus)="closed") AND ((tblContacts.ContactType)
Like "*coordinator*"));


TIA

 

[John Spencer]

You could use a subquery in the where clause

SELECT ([firstname] & " " & [lastname]) AS CombName,
tblContacts.ContactStatus
, tblContacts.ContactType
, tblRegions.Region
FROM tblRegions INNER JOIN tblContacts
ON tblRegions.RegionID = tblContacts.RegionFK
WHERE tblContacts.ContactStatus="closed"
AND tblContacts.ContactType Like "*coordinator*"
AND tblRegions.RegionID in
(SELECT R.Region
FROM tblRegions as R INNER JOIN tblContacts as C
R.RegionID = C.RegionFK
WHERE C.ContactType like "*Coordinator*"
GROUP BY Region
HAVING Count(Region) > 1)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sue Compelling]

Hi John

Thx so much for the response.... the query has come back with a syntax error
on the WHERE clause ... (the error message starts from the first WHERE) but
it set me on a train of thought where I was able to conquer it myself. I'm
not flash on code and the subquery code writing is beyond me so I had to do
two seperate queries and reference one - but it works fine.

Thanks again.


--
Sue Compelling

You could use a subquery in the where clause

SELECT ([firstname] & " " & [lastname]) AS CombName,
tblContacts.ContactStatus
, tblContacts.ContactType
, tblRegions.Region
FROM tblRegions INNER JOIN tblContacts
ON tblRegions.RegionID = tblContacts.RegionFK
WHERE tblContacts.ContactStatus="closed"
AND tblContacts.ContactType Like "*coordinator*"
AND tblRegions.RegionID in
(SELECT R.Region
FROM tblRegions as R INNER JOIN tblContacts as C
R.RegionID = C.RegionFK
WHERE C.ContactType like "*Coordinator*"
GROUP BY Region
HAVING Count(Region) > 1)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Evening

I have a select query that extracts constituents who have a "status" of
closed and a "type" of coordinator.

I now want to only retrieve those records where there is more than one
Coordinator per Region. Is this possible?

My code is ...

SELECT ([firstname] & " " & [lastname]) AS CombName,
tblContacts.ContactStatus, tblContacts.ContactType, tblRegions.Region
FROM tblRegions INNER JOIN tblContacts ON tblRegions.RegionID =
tblContacts.RegionFK
WHERE (((tblContacts.ContactStatus)="closed") AND ((tblContacts.ContactType)
Like "*coordinator*"));


TIA