recursive query?

  • Thread starter Thread starter Shivalee Gupta via AccessMonster.com
  • Start date Start date
S

Shivalee Gupta via AccessMonster.com

i have this query:

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND (
(agr_1251.LOW)="f110"))
OR
(((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch" And
(agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3"
And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25" And
(agr_1251.LOW)=""))
OR
(((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch" And
(agr_1251.FIELD)="koart") AND ((agr_1251.LOW)="2" And (agr_1251.LOW)="3"
And (agr_1251.LOW)="11" And (agr_1251.LOW)="21" And (agr_1251.LOW)="25" And
(agr_1251.LOW)=""));

it is just a simple query which shows a name depending on all the 3 grouped
values listed.
if there are 10 records in a file, and this query is for the first record,
can i attach this query to the first record? so i can run this query
recursively for the rest of the 9 records?

please help.
shivalee
 
This doesn't make much sense. The 2nd Or condition will never retrun any
values as agr_1251.LOW cannot equal 2 and 3 and 11 and 21 and 25 and "" . ..
.. all at the same time . . .

Nor can the the 3rd Or condition retrun anything. Again agr_1251.LOW cannot
equal 2 and 3 and 11 and 21 and 25 and "" . .. . all at the same time . .
..

The only bit that actually returns anything is the first bit
Where ((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND
((agr_1251.LOW)="f110"))

I'd suggest you revisit some of the query logic, and perhaps simplify the
question.
 
ok, so i changed the query to:

SELECT agr_1251.AGR_NAME
FROM agr_1251
WHERE (((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND (
(agr_1251.LOW)="f110"))
OR
(((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch") AND (
(agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And
(agr_1251.LOW)="21" And (agr_1251.LOW)="25"))
OR
(((agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)=""))
OR
(((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch") AND (
(agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And
(agr_1251.LOW)="21" And (agr_1251.LOW)="25"))
OR
(((agr_1251.FIELD)="koart") AND ((agr_1251.LOW)=""));

this does work!
my problem is not how thw query is working! but if there are 10 records in
a file, and this query is for the first record,
can i attach this query to the first record? so i can run this query
recursively for the rest of the 9 records?

shivalee
 
But you have the same problem. The following statement will ALWAYS be false,
irrespective of the data

((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And
(agr_1251.LOW)="21" And (agr_1251.LOW)="25"))

The value of the field LOW cannot simultaneously be more than one value.
It'a a bit like saying I am 2, 3, 11, 21 and 25 years old all on the same
day.
 
o.k i got it. so can you tell me how do i go about doing it? have u
actually understood what i want to do in this query? otherwise i can send
you what i want.
shivalee
 
Shivalee Gupta via AccessMonster.com said:
o.k i got it. so can you tell me how do i go about doing it? have u
actually understood what i want to do in this query? otherwise i can send
you what i want.
shivalee
Click to expand...

Please post it here.

it is just a simple query which shows a name depending on all the 3 grouped
values listed.
if there are 10 records in a file, and this query is for the first record,
can i attach this query to the first record? so i can run this query
recursively for the rest of the 9 records?

please help.
shivalee
Click to expand...


S. Gupta,

It's already been pointed out how the original conditional AND logic
always evaluates to False.


As to the question presented below the original query (which I copied
above).

The answer is that Relational Database Tables are "sets" of data. A
Query operates on *all* rows of a Table, in other words, a "set"
operation.

You can specify columns to keep (by naming them on the SELECT clause),
or rows to keep (by using the WHERE clause) in the final output *set*.

As each Table is a "set", it has no "first" row. Just because MS
Access displays a Table's dataset with the first row in the sort-order
of the Primary Key at the top of the datasheet (unless the display
sort-order has been changed), does *not* mean that is the "first" row
in the Table.

Access has various features, like TOP, etc., which allow you to get
what *appears* to be the "first" row. But all that such operations
retrieve is the first row of the sort-order. Different sort-orders
can retrieve different rows.

So, the short answer to the question of: "Can I attach a query to a
row", is no.

If, somehow, you will always know what the Primary Key value of the
row is, you can hard-code that value into your Query on the WHERE
clause, and the Query will always retrieve that *specific* row (not
the "first" row, the row *specified*). But that query will only work
on that *row*.

You can, if you want to take it further, write VBA which dynamically
assembles new SQL, each time changing the reference to the Primary
Key.


However, none of this matters. The AND logic of the Query, which
specifies that the agr_1251.LOW must be equal to multiple values at
the same time, which will *never* happen, and prevents that Query from
ever retrieving a single row.
 
Back
Top