Access 2003 SQL sub-query within query
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.
Software/Hardware used:
Access 2003
Software/Hardware used:
Access 2003
ASKED:
May 2, 2012 1:16 AM
UPDATED:
May 3, 2012 12:40 PM
Answer Wiki:
Hello, everybody, the good shoping place, the new season approaching, click in.
Welcome to ==== http://www.fashion-long-4biz.com ==
Air Jordan (1-24) shoes $35
Jordan (1-22)&2009 shoes $45
Nike shox (R4, NZ, OZ, TL1, TL2, TL3) $35
Handbags ( Coach Lv fendi D&G) $30
T-shirts (polo, ed hardy, lacoste) $14
Jean (True Religion, ed hardy, coogi)$34
Sunglasses ( Oakey, coach, Gucci, Armaini)$15
New era cap $16
Biki ni (Ed hardy, polo) $18
FREE SHIPPING
==== http://www.fashion-long-4biz.com ==
http://www.fashion-long-4biz.com
http://www.fashion-long-4biz.com
To see all answers submitted to the Answer Wiki: View Answer History.
So far it sounds like you’re on a good path. Please show the query that you have developed. It can probably be changed to help get you closer.
Tom
Thanks TomLiotta
Here’s the query.
SELECT Trans_Person.FirstName, Trans_Person.LastName, Trans_Person.Phone_Mobile, Trans_Study.ExamPass, Trans_Study.ExamLevelID
FROM Trans_Person LEFT JOIN Trans_Study ON Trans_Person.ID = Trans_Study.PersonID
WHERE (((Trans_Study.ExamPass)<>”pass” Or (Trans_Study.ExamPass) Is Null) AND ((Trans_Study.ExamLevelID)=1 Or (Trans_Study.ExamLevelID) Is Null));