title: If Then Else in WHERE clause in MS Access SQL Query
body: <p>I'm trying out MS Access SQL Query. My Data is structured like this
  <a href="http://i.stack.imgur.com/vABEb.jpg" rel="nofollow">Rent Table</a></p>
  
  <p>The idea is I want to split the table to collect the latest start up to 12 months back using [Year_Start] and [Month_Start] as basis. So my rough code will be:</p>
  
  <pre><code>SELECT [Renter_Name], [Amount]
  FROM RentTable1
  WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start])
  ORDER BY [Renter_Name];
  </code></pre>
  
  <p>Subsequently, other month tables will conceptually be coded like this:</p>
  
  <pre><code>SELECT [Renter_Name], [Amount]
  FROM RentTable1
  WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - 1
  ORDER BY [Renter_Name];
  </code></pre>
  
  <p>And then subsequent months will be adjusted using the minus sign.</p>
  
  <pre><code>SELECT [Renter_Name], [Amount]
  FROM RentTable1
  WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - 2
  ORDER BY [Renter_Name];
  </code></pre>
  
  <p>I'm also considering a case where [Month_Start] = Max([Month_Start]) - x will be zero (0) or a negative number so a theoretical code will be:</p>
  
  <pre><code>    SELECT [Renter_Name], [Amount]
      FROM RentTable1
      IF Max([Month_Start]) - X &lt;= 0 THEN 
         WHERE [Year_Start] = Max([Year_Start]) - 1 AND [Month_Start] = Max([Month_Start]) - X
      ELSE
         WHERE [Year_Start] = Max([Year_Start]) AND [Month_Start] = Max([Month_Start]) - X
      END IF
      ORDER BY [Renter_Name];
  </code></pre>
  
  <p>*** X being the months backward from the latest start month and year.</p>
  
  <p>Clearly, you see my SQL coding skills are really weak. Pardon me as I'm really a beginner. So there are some touches other standard programming like If-Then-Else statements.</p>
  
  <p>I was hoping someone could propose to correct the above codes.</p>
  
  <p>Thanks! Appreciate everyone who stumble upon this question.</p>
  
  <p>EDIT 1:</p>
  
  <p>Just to clarify, this is the expected thought:</p>
  
  <p>In the example the latest period is 2016 and 4. So it should pick it up for TABLE1.</p>
  
  <p>A subsequent query is to be made to minus one month from the latest period so the result should be 2016 and 3. This goes on until 2016 and 1.</p>
  
  <p>When 4 - 4 happens which equals 0, the query should be able to skip through this illogical step and go through (2016 - 1) and the get the max month using the result of (2016 - 1) which is 2015 and 12.</p>