Post reply

Does Access support the EXISTS keyword?

  • March 10, 2015 at 2:12 am

    #305599

    I don't think so... but I was wondering if anyone else found out different. I was trying to use it to do something simple... find the number of companies with employees attending an event. EXISTS is perfect for that.

    -- count of unique orgs with Employees attending the event:

    SELECT COUNT(*) AS CountOfEmployers

    FROM Company c

    WHERE EXISTS (

    SELECT e.EmployerID -- same as companyID

    , a.EventID

    --, e.EmployeeID

    --, e.FirstName

    FROM (Employee e INNER JOIN Attendance a ON e.EmployeeID = a.AttendeeID) INNER JOIN Events ev ON a.EventID = ev.EventID

    WHERE e.EmployerID = c.CompanyID

    );

    but when I tried to do the same in Access it failed. So I guess I'd need to use DCount or some other ugly thing? (Not a fan!)

  • March 10, 2015 at 8:46 am

    #1782953

    I believe it does, although I tend to avoid subqueries for this sort of thing and use a GroupBy. Take a look at Intermediate Microsoft Jet SQL for Access 2000 - it contains a description of the EXISTS predicate about two-thirds of the way to the bottom of the article.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • March 10, 2015 at 10:27 am

    #1782995

    Guess I'll have to check my syntax.

    I would have thought EXISTS will work better because theoretically it stops looking as soon as the condition is false.

    Thanks Wendell!

    Pieter

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply