Finding Non-Repeated Records in a Query

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

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???

select * from table where status"cancel"

-----Original Message-----
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



.

On Thu, 22 Jul 2004 12:34:19 -0400, "Chris Guimbellot"
wrote:

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")

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

John,

That worked like a champ. Thanks a lot.

Chris

"John Vinson" wrote in message
...
On Thu, 22 Jul 2004 12:34:19 -0400, "Chris Guimbellot"
wrote:

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")

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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" wrote in message
...
On Thu, 22 Jul 2004 12:34:19 -0400, "Chris Guimbellot"
wrote:

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")

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

On Thu, 22 Jul 2004 16:26:57 -0400, "Chris Guimbellot"
wrote:

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.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public