MS-Access SQL – how to implement a boolean sub-query?

I have a membership database, with linked tables: members and history, where the history includes any update about the member, including joining date and leaving date in relevant cases.

This means that within the history table there may be a few different records related to a member ID.

I’d like to create a query that would result in a spreadsheet showing a member id, name and also the Membership Status – which will be “ON” as long as that member doesn’t have a “leave” action in the history table (let’s say number 3). It may have different actions, and in different order in the history table (so “leave” is not necessarily the last action).

Thus, the status displayed should be “OFF” if a “leave”(3) record is found related to that member among all records related to that member in history. Otherwise, it’s “ON”.

I tried something like the following, but it doesn’t work, as the Exists part always returns true:

SELECT DISTINCT 
Members.ID, 
Members.MemberName AS Name,
IIF(
 EXISTS(
  SELECT DISTINCT *
   FROM Members LEFT JOIN History ON Members.ID = History.MemberID
   WHERE (History.UpdateType <> 3)
  ),"ON", "OFF"
)
AS Status
FROM Members
ORDER BY Members.ID;

Thank you for any advice…

Answer

You need to correlate the subquery with the outer query. Also, I if followed you correctly, you want to check if any related history record has type = 3, and set the status to 'OFF' in this case.

SELECT 
    Members.ID, 
    Members.MemberName AS Name,
    IIF(
        EXISTS(
            SELECT 1
            FROM History 
            WHERE (Members.ID = History.MemberID AND History.UpdateType = 3)
        ),
        'OFF', 
        'ON'
    ) AS Status
FROM Members
ORDER BY Members.ID;

Side note: you don’t need DISTINCT in an EXISTS subquery (it is there to check if anything is there); likely, you don’t need it in the outer query either.