I’m trying to extract all Person.ID eligible to sit a Level 1 study exam from our database.
So I setup a query to look for members with study exam level 1 not equal to “pass” or Is Null, thinking it would give me all people who hadn’t sat or hadn’t passed level one exam. However where it doesn’t work is where a person has sat and failed and then later sat and passed. Because the query looks at it on a record by record basis any person who failed is picked up regardless if they then passed at a later time. I think what I need is to somehow look at all study exam records for a person and if there they have no level 1 pass in any of their study records then include them in the list.