Pick up students who enrolled the second semester but dropped the third semester

  • Thread starter Thread starter Charles
  • Start date Start date
C

Charles

Hi All:

I am trying to select those students who were enrolled
in their second fall, but dropped out in the third. I
need to get those people's record the fall before for
GPA. For example:

ID YR ENRL GPA
2 2 1 2.5
2 3 0 ..
3 2 1 3.2
3 3 1 3.3
4 2 0 ..
4 2 0 ..
5 2 1 1.9
5 3 0 ..

Only ID 2 and 5 meet my selection criteria and I need
to retain the record that has values for GPA (the
first record of the person if enrolled in Yr2 and
dropped in Yr3). I know someone out there can do this
easily. Thanks a lot!


Charles
 
Charles said:
I am trying to select those students who were enrolled
in their second fall, but dropped out in the third. I
need to get those people's record the fall before for
GPA. For example:

ID YR ENRL GPA
2 2 1 2.5
2 3 0 ..
3 2 1 3.2
3 3 1 3.3
4 2 0 ..
4 2 0 ..
5 2 1 1.9
5 3 0 ..

Only ID 2 and 5 meet my selection criteria and I need
to retain the record that has values for GPA (the
first record of the person if enrolled in Yr2 and
dropped in Yr3).
Click to expand...

Let's make a sequence of queries that break the problem down
into managable pieces.

This one will select all the people that were enrolled in
year 2.
Query Fall2EnRolled:
SELECT ID, GPA
FROM thetable
WHERE Yr = 2 And Enrl <> 0

This one will select all the people that were dropped in
year 3
Query Fall3Dropped:
SELECT ID
FROM thetable
WHERE Yr = 3 And Enrl = 0

Now you can join the two queries to select only the people
that meet both criteria:

SELECT Fall2EnRolled.ID, Fall3Dropped.GPA
FROM Fall2EnRolled INNER JOIN Fall3Dropped
ON Fall2EnRolled.ID = Fall3Dropped.ID
 
Thank you so much.

Charles
-----Original Message-----


Let's make a sequence of queries that break the problem down
into managable pieces.

This one will select all the people that were enrolled in
year 2.
Query Fall2EnRolled:
SELECT ID, GPA
FROM thetable
WHERE Yr = 2 And Enrl <> 0

This one will select all the people that were dropped in
year 3
Query Fall3Dropped:
SELECT ID
FROM thetable
WHERE Yr = 3 And Enrl = 0

Now you can join the two queries to select only the people
that meet both criteria:

SELECT Fall2EnRolled.ID, Fall3Dropped.GPA
FROM Fall2EnRolled INNER JOIN Fall3Dropped
ON Fall2EnRolled.ID = Fall3Dropped.ID
Click to expand...
 
Back
Top