Finding Non-Repeated Records in a Query

[Chris Guimbellot]

Hello,

Access2K.

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.

Sorry that I may not be asking this question correctly, but is this
possible? Thanks,

Chris

 

[xxplusxx]

i guess you should base your query or consider also
transaction status.
the reason for these is what if you have the same item
number but an addition new transaction???

 

[John Vinson]

Hello,

Access2K.

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.

You can use a NOT EXISTS clause in the query:

SELECT yourtable.*
FROM yourtable
WHERE NOT EXISTS
(SELECT Transaction FROM yourtable AS X
WHERE X.Transaction=yourtable.Transaction
AND X.OrderStatus = "Cancel")

 

[Chris Guimbellot]

John,

That worked like a champ. Thanks a lot.

Chris

 

[Chris Guimbellot]

John,

Is there any way to modify that code 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

 

[John Vinson]

Is there any way to modify that code so cancelled orders whose original
order is not in the recordset are still included.

Sure - just include additional criteria in the subquery to select only
those records that you want to exclude.