MS Access query Comparing Number and Display
<pre><code>SELECT Student_Number,
DATEPART("yyyy", Starting_Date) AS Starting_Year,
DATEPART("yyyy",Ending_Date) AS Ending_Year
FROM   PGME
WHERE  ID = 1
</code></pre>

<p>Display as below</p>

<pre><code>Student_Number    Starting_Year Ending_Year
003329497         1994          1999
</code></pre>

<p>But what I need is comparing the "Starting_Year" and "Ending_Year" (<strong>they are randomly date</strong>), when "Ending_Year" is bigger than "Starting_Year", it will display a new row until it the "Starting_Year" is equal to "Ending_Year". (I am expecting to get the following result)</p>

<pre><code>Student_Number    Starting_Year Ending_Year
003329497         1994          1999
003329497         1995          1999
003329497         1996          1999
003329497         1997          1999
003329497         1998          1999
003329497         1999          1999
</code></pre>

<p>Student_Number(1)</p>

<pre><code>Student_Number    Starting_Year Ending_Year
002442895         1993          2000
002442895         1994          2000
002442895         1995          2000
002442895         1996          2000
002442895         1997          2000
002442895         1998          2000
002442895         1999          2000
002442895         2000          2000
</code></pre>

<p>Student_Number(2)</p>

<p>There are more than 100 "Student_Number" I need to do with. I am thinking using "if" statement to do it, but can not figure it out. Any ways to do with it?</p>