Query out transactions Continued..

  • Thread starter Thread starter Mike
  • Start date Start date
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)

..
 
Describe "still not working". What is wrong? Error message? Wrong records
returned?
 
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...
 
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...
 
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?
 
Back
Top