Quering out transactions

[Mike]

The following doesn't seem to be working. It seems to get the computer bogged
down. Is there another solution to this? or can someone show me how to do
this in design view?

Thanks in advance.


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

 

[Krzysztof Naworyta]

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

--
KN


Juzer Mike <[email protected]> napisa³
| The following doesn't seem to be working. It seems to get the computer
| bogged down. Is there another solution to this? or can someone show me
| how to do this in design view?
|
| Thanks in advance.
|
|
| 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
|
|
|
| || 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.

--
KN

archiwum grupy:
http://groups.google.pl/advanced_search
(grupa: pl*msaccess)