writing Query Urgent!

[mapat]

Hello,

I am having trouble writing the following query:

All Students that in the first year were Qualified (Qualified=Yes) but the next or any following year were NOT Qualified (Qualified=No)

This is the source table sample:

StudentID StudentFirst StudentLast Start Year Qualified
2CSP000010 Stephanie Smith 2005 2005 Yes
2CSP000010 Stephanie Smith 2005 2008 Yes
2CSP000010 Stephanie Smith 2005 2007 Yes
2CSP000010 Stephanie Smith 2005 2006 Yes
2CSP000011 Ashe Brewster 2005 2005 Yes
2CSP000011 Ashe Brewster 2005 2006 No
2CSP000012 Angel Stockton 2005 2005 Yes
2CSP000012 Angel Stockton 2005 2006 No

So this would only return Ashe, and Angel


Thank you very much everybody

 

[MSAccessRookie]

Hello,

I am having trouble writing the following query:

All Students that in the first year were Qualified (Qualified=Yes) but the next or any following year were NOT Qualified (Qualified=No)

This is the source table sample:

StudentID StudentFirst StudentLast Start Year Qualified
2CSP000010 Stephanie Smith 2005 2005 Yes
2CSP000010 Stephanie Smith 2005 2008 Yes
2CSP000010 Stephanie Smith 2005 2007 Yes
2CSP000010 Stephanie Smith 2005 2006 Yes
2CSP000011 Ashe Brewster 2005 2005 Yes
2CSP000011 Ashe Brewster 2005 2006 No
2CSP000012 Angel Stockton 2005 2005 Yes
2CSP000012 Angel Stockton 2005 2006 No

So this would only return Ashe, and Angel


Thank you very much everybody

I believe that you can use a Sub-Query to do this.

The MAIN QUERY selects information from the table where a record has Yes in the Unqualified Field for the first year (Start=Year), and the StudentID matches the results from the SUB QUERY.

The SUB QUERY returns distinct Student IDs where a record has No in the Unqualified Field, and the year is not the start year (Start<>Year).

Note that the Field Year has a name that is used as a function in MS Acess { Year() } , and should be changed if possible.

 

[mapat]

I believe that you can use a Sub-Query to do this.

The MAIN QUERY selects information from the table where a record has Yes in the Unqualified Field for the first year (Start=Year), and the StudentID matches the results from the SUB QUERY.

The SUB QUERY returns distinct Student IDs where a record has No in the Unqualified Field, and the year is not the start year (Start<>Year).

Note that the Field Year has a name that is used as a function in MS Acess { Year() } , and should be changed if possible.

This is what I have so far and it's not working. Am I on the right track?

SELECT t1.*
FROM tempAllStudents t1
WHERE t1.Start = t1.CYear AND t1.Qualified = "Yes"
AND t1.[Student ID] = (SELECT t2.[Student ID]
FROM tempAllStudents t2
WHERE t2.[Student ID] = t1.[Student ID] AND t2.Start <> t2.CYear AND t2.Qualified = "No")


Thanks again

 

[MSAccessRookie]

This is what I have so far and it's not working. Am I on the right track?

SELECT t1.*
FROM tempAllStudents t1
WHERE t1.Start = t1.CYear AND t1.Qualified = "Yes"
AND t1.[Student ID] = (SELECT t2.[Student ID]
FROM tempAllStudents t2
WHERE t2.[Student ID] = t1.[Student ID] AND t2.Start <> t2.CYear AND t2.Qualified = "No")


Thanks again

The MAIN QUERY looks like it could be right, but the SUB QUERY can be simplified.

SELECT DISTINCT t2.[Student ID]
FROM tempAllStudents t2
WHERE t2.Start <> t2.CYear AND t2.Qualified = "No")

I do not think that there is a need to refer to the MAIN QUERY within the SUB QUERY, since they are already joined on the [Student ID] Field. Just collecting all records that match, and using (DISTINCT) to eliminate the duplicates should be enough.