Controlled multiple results in Access SQL. Find all n matches

Question

Given a database of invoices that also includes cancellation invoices (=credit notes or CNs). I want to select the cancelled invoices for flagging them. To link from credit note to original invoice, we assume customer & 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).

My statement to identify credit notes, referred to later as "CancelInvs" (CN is a yes/no flag)

SELECT * FROM Invoices WHERE CN

Below simply finds cancelled invoices but, in case multiple cancellations exist, it only finds one original, repeating it multiple times with each additional CN.

SELECT Min(InvNum) FROM Invoices 
INNER JOIN CancelInvs ON Invoices.Customer = CancelInvs.Customer AND Invoices.Amount = CancelInvs.Amount 
GROUP BY CancelInvs.InvNum

I need to also find the other ones to be flagged as cancelled. No problem to add VBA if needed. Thanks for your attention.

Sample data: sample data img

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.

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.

Answer 1

Let us leave it. Proved to be too complex so simple Select queries can not handle. Decided to do vba cycle and manage flagging by using tmp tables. Thank you all.

A stupidly complex procedure was finally used due to the nature of this real life task. My SQL includes VBA-controlled adaptive parameter. Just to give an insight, here is the process (in form of human-friendly script).

Begin cycle
Count and collect matching Invoices per CreditNote (criteria: same repairer & customer & amount, not cancelled)
Flag lines in collection table as 'Duplicate' where same Invoice appears for several CNs
Process the 1-1 matches by flagging Invoices as 'Cancelled'
Flag the processed CNs as 'Done', in CN matches collection table
If there are no 1-1 matches, allow multiple matches for CNs for one cycle - find earliest occurence of Invoice matches
Loop cycle while any matches remain
Drop any unpaired CN

Answer 2

Is InvNum is a primary key, a unique identifier for an invoice? Then let the CN table have a field to hold that value. This will let you JOIN in a decent way.

SELECT InvNum FROM Invoices 
INNER JOIN CancelInvs 
    ON Invoices.InvNum = CancelInvs.InvNum 

You must have a way to positively identify the original from the CN. Typically that would be InvNum, stored in the CN as a foreign key. Without that, you have been using Customer and Amount. But that combo doesn't let you positively identify the original. Therefore you can't do what you want.