Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: Pick M or O but not both!

    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.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Pick M or O but not both!

    Originally posted by 130213
    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.
    Is it possible for you to post a copy of your db?
    It would be easier, for me at least, to work this problem.
    If you find an answer from one of the others on the forum then disregard the request.

    Good luck

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •