title: Controlled multiple results in Access SQL. Find all n matches
body: <p>Given a database of invoices that also includes cancellation invoices (=credit notes or CNs).
  I want to select the <strong><em>cancelled</em></strong> invoices for flagging them.
  To link from credit note to original invoice, we assume customer &amp; amount matches.
  Problem: There are multiple CNs that fall in above creiteria, so I need to find each and every cancelled originals.
  E.g. if there are 5 CNs, then we may find 6 original invoices, and 5 of them should be flagged assuming the most recent one valid (no flag for this latest).</p>
  
  <p>My statement to identify credit notes, referred to later as "CancelInvs" (CN is a yes/no flag)</p>
  
  <p>SELECT * FROM Invoices WHERE CN</p>
  
  <p>Below simply finds cancelled invoices but, in case multiple cancellations exist, it only finds one original, repeating it multiple times with each additional CN.</p>
  
  <pre><code>SELECT Min(InvNum) FROM Invoices 
  INNER JOIN CancelInvs ON Invoices.Customer = CancelInvs.Customer AND Invoices.Amount = CancelInvs.Amount 
  GROUP BY CancelInvs.InvNum
  </code></pre>
  
  <p>I need to also find the other ones to be flagged as cancelled. No problem to add VBA if needed. Thanks for your attention.</p>
  
  <p>Sample data:
  <a href="http://i.stack.imgur.com/jP5uK.png" rel="nofollow">sample data img</a></p>
  
  <p>Invoices and CreditNotes are in the same table. For simplicity, I made both amounts as positive. 
  Those cancelled by means of a matching CN shall be flagged. Valid ones need no flagging. A CN may be preceeded by more than one matching invoices. In such case, only the first one is to be flagged.</p>
  
  <p>In the sample, record 1400 is a CreditNote. The only invoice before it, with matching parametres is record 1003. So, 1003 shall be flagged as 'Cancelled'.
  The next CN is 1580. An invoice before it, with matching fields is 1412, so 1412 shall be flagged as 'Cancelled'. 1003 is already out of the game. No more unused CreditNotes, so nothing more invoice to be flagged. </p>