title: Achieving ROW_NUMBER / PARTITION BY in MS Access
body: <p>How can you make use of &quot;Row number over partition by&quot; in MS access? I googled it, but couldn't find any information as to how one would go about doing this. I'm talking about this particular function, which assigns a sequential integer to each row within the partition of a result set:</p>
  <pre><code>RowNumber Over(Partition by city Order By EmployeeID)
  </code></pre>
  <p>My data looks like this:</p>
  <pre><code>DOC_TYPE    Ino
  3a  1800xxc1
  3b  1810xxc2
  3c  1700xxc3
  3a  1700xxc4
  3a  1800xxc5
  3a  1800xxc6
  3b  1800xxc7
  </code></pre>
  <p>However, I need it to look like this:</p>
  <pre><code>DOC_TYPE    Ino Seq
  3a  1800xxc1    1
  3a  1700xxc4    2
  3a  1800xxc5    3
  3a  1800xxc6    4
  3b  1810xxc2    1
  3b  1800xxc7    2
  3c  1700xxc3    1
  </code></pre>
  <p>This is my query:</p>
  <pre><code>SELECT t1.RT_TAXCODE, t1.INV_NO, COUNT(*) AS Sno
  FROM GroupByTAXCODE AS t1 INNER JOIN GroupByTAXCODE AS t2 ON (t2.RT_TAXCODE = t1.RT_TAXCODE) AND (t2.Inv_no &lt;= t1.Inv_no)
  GROUP BY t1.RT_TAXCODE, t1.INV_NO
  HAVING COUNT(*)=1
  ORDER BY 1, 3;
  </code></pre>
  <p>This is taking more time as f 30 seconds</p>