Hi,

I am still struggling with this query which should pick M or O but not both!


SELECT First(Centre.ECDL_ID) AS FirstOfECDL_ID, [Title] & " " & [Forename] & " " & [Surname] AS Contact, Centre.centrename, Centre.address1, Centre.address2, Centre.town, Centre.county, Centre.postcode, CentreContact.EMail, CentreContactType.CentreContactType

FROM ((Centre INNER JOIN CentreContact ON Centre.CentreID = CentreContact.CentreID) INNER JOIN CentreStatus ON Centre.StatusID = CentreStatus.StatusID) INNER JOIN CentreContactType ON CentreContact.CentreContactTypeID = CentreContactType.CentreContactTypeID

GROUP BY [Title] & " " & [Forename] & " " & [Surname], Centre.centrename, Centre.address1, Centre.address2, Centre.town, Centre.county, Centre.postcode, CentreContact.EMail, CentreContactType.CentreContactType, CentreStatus.Status, Centre.OMR

HAVING (((CentreContactType.CentreContactType)="M" Or (CentreContactType.CentreContactType)="0") AND ((CentreStatus.Status)="AD") AND ((Centre.OMR)=-1))

ORDER BY First(Centre.ECDL_ID);


I tried the UNION query but it still included both Contacts from the same Centre whose CentreContactType’s were M and O, I want to say if M exists show M but if BOTH M and O exist at the same Centre show O instead of M.

Any help woul be very appreciated.

Jnr.

Hi,

did you try the

select case when
from...-statement

select case
when value = 'M'
then 'M'
when value = 'O' or 'M'
then 'o'
from...
where....

Thanks, i will try that.....

its really bugging me!

Jnr