Query out transaction

[Mike]

I have a table with data that includes Division, store, amount, and line
(which describes the transaction as a paid deposit, tax, rts which means
return to stock) that are the columns. I also have a concatenation which has
by store # - transaction # as my unique identifier.

I would like to query out all the transactions that do not have a "rts"
associated with it. So if it does not have an rts in the transaction, then I
want it to show. If not, I do not want the entire transaction.
Thanks in advance.
Example:
Concatenation Division Store Trans# Line
056-45789 03 056 45789 paydp
056-45789 03 056 45789 tax
056-45789 03 056 45789 rts
052-98678 08 052 98678 tend
052-98678 08 052 98678 tax

In this case, I do not want concatenation 056-45789 to show because it has
an rts. That includes the paydp, tax and rts.

 

[vanderghast]

SELECT *
FROM tableName As a
WHERE NOT "rst" = ANY(SELECT line
FROM tableName As b
WHERE b.[trans#] = a.[trans#])



Note that you can change the where clause to:

WHERE NOT "rst" IN(SELECT line
FROM tableName As b
WHERE b.[trans#] = a.[trans#])



both are equivalent.


Vanderghast, Access MVP