Non-Repeated Records in a Query (Modified from my post yesterday)

[Chris Guimbellot]

Hello,

Access2K. I posted the following question yesterday (by the way, thanks
John), but have a tweak to it and cant figure out the answer. My new
question is below the copy of the original:

I have a table or orders that records data in a way that when an order is
placed, then cancelled, the data looks as follows (the important rows are 1
and 3. notice the transaction column):

ID Transaction Item QTY Price OrderStatus
1 1000 123 1 10.00 New
2 1001 124 5 100.00 New
3 1000 123 -1 -10.00 Cancel

Is there any way when I query this table, I can retrieve only the rows that
do not have cancelled transactions? Something like querying to say if the
transaction number appears more than once, throw out all of the records with
that transaction number.

My new question is is there any way to make it so cancelled orders whose
original order is not in the recordset are still included. The reason is
because orders may be placed during one period of time and cancelled during
another. I run monthly invoices and an order placed this month must be
invoiced. Next month, though, it may be cancelled, and the invoice for that
month needs to show the cancellation so the cash can be credited. Any ideas?
Thanks,

Chris