Query out transactions Continued..

M

Mike

The following statement is still not working. A couple of questions: 1) Is my
tablename suppose to be in brackets? 2) Is the [trans#] suppose to be my
concatenation?




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)

..
 
J

Jerry Whittle

Describe "still not working". What is wrong? Error message? Wrong records
returned?
 
M

Mike

The query seems to freeze up after letting it run for about 10 minutes and no
records are returned. When I enter in that statement in sequel view, and
change it to design view, it seems to be incomplete.

Thanks.


Jerry Whittle said:
Describe "still not working". What is wrong? Error message? Wrong records
returned?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Mike said:
The following statement is still not working. A couple of questions: 1) Is my
tablename suppose to be in brackets? 2) Is the [trans#] suppose to be my
concatenation?




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)

.
Click to expand...
Click to expand...
 
J

Jerry Whittle

Design view can't handle some types of queries properly such as Union,
Exists, and a few others. I suggest not switching to Design View and just
staying in SQL View.

One rule of thumb that I have is if an Exists query is slow, change it to an
In query. And the opposite is also true. See if this works:

SELECT *
FROM tableName As A
WHERE A.[trans#] NOT IN (SELECT B.[trans#]
FROM tableName As B
WHERE B.line = "rst") ;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Mike said:
The query seems to freeze up after letting it run for about 10 minutes and no
records are returned. When I enter in that statement in sequel view, and
change it to design view, it seems to be incomplete.

Thanks.


Jerry Whittle said:
Describe "still not working". What is wrong? Error message? Wrong records
returned?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Mike said:
The following statement is still not working. A couple of questions: 1) Is my
tablename suppose to be in brackets? 2) Is the [trans#] suppose to be my
concatenation?




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)

.
Click to expand...
Click to expand...
Click to expand...
 
K

Krzysztof Naworyta

Mike wrote:
| The query seems to freeze up after letting it run for about 10
| minutes and no records are returned. When I enter in that statement
| in sequel view, and change it to design view, it seems to be
| incomplete.
|

|| Describe "still not working". What is wrong? Error message? Wrong
|| records returned?


How big is your table ?
Does it have indexes on fields used in criteria?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query out transaction 1
Quering out transactions 1
Populate empty cells to use for conversion 1
Crosstab Query 0
How to use the same date range parameters for multiple reports 2
Criteria in Query cannot find table name 0
Running update query only once. 0
Key violation on Append Query 1

Top