very complex-this query!

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

Shivalee Gupta via AccessMonster.com

I have a table called agr_1251 in access 2000. the structure is:
1st col: sodid
2nd col:object
3rd col:field
4rth col:value.

the rows look like this:
sodid object field1 value1 field2 value2
2 S_TCODE TCD FK01
2 S_TCODE TCD F-43
2 F_LFA1_APP ACTVT 01 APPKZ F
2 F_LFA1_BUK ACTVT 01 BUKRS

Can anyone tell me how to write a query for the above structure. It should
return a name that is equal to "ALL" conditions as true for the above
structure.

thanks for any or all help,
regards,
shivalee
 
What are the "ALL" conditions?

What are you wanting to do? It's not clear from your post.
 
I shall explain: If you see my query down below, u can paste it in your new
query and then see the design view.

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 query is actually the type of my requirement, but having 2 problems:
1) In the 1st line of "where", just when it encounters an OR, it just
does'nt execute the lines after that. It shows me records pertaining to the
1st where clause before the OR. where as I would like all the parts of the
query to run as "AND".
2) Also, if seen individually, I have a column called low, which has 5
values. I would like to check for all 5 values as "AND" and not as "OR".
If it is not maybe possible via query, is it maybe possible via coding?
Any suggestions are most welcome.
thanks for your time,
shivalee
 
Before I can try to give an answer re: "All" items, etc., I see another
problem in the query that is causing your query to not run correctly.

Take a look at this "groupings" of a WHERE condition that must all be met:

(((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"))

Note that, in order for the above group to produce a "TRUE" value so that
the record would be selected, the agr_1251.LOW field must have the value of
"2", "3", "11", "21", and "25" all at the same time. This obviously is not
possible. Perhaps you want an OR condition here?

(((agr_1251.OBJECT)="f_regu_buk") AND ((agr_1251.FIELD)="fbtch") AND (
(agr_1251.LOW)="2" OR (agr_1251.LOW)="3" OR (agr_1251.LOW)="11" OR
(agr_1251.LOW)="21" OR (agr_1251.LOW)="25"))


Similarly, this grouping seems to have the same problem:

(((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)=""));

Should it be this:

(((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch") AND (
(agr_1251.LOW)="2" OR (agr_1251.LOW)="3" OR (agr_1251.LOW)="11" OR
(agr_1251.LOW)="21" OR (agr_1251.LOW)="25"))
OR (((agr_1251.FIELD)="koart") AND ((agr_1251.LOW)=""));

If these changes are correct, make them in your query and then let's see if
you're still having the same problem that you noted. Post back and we'll
continue.
 
Hello ken.
That is actually my problem that I wish to have and use "ALl" instead of
"OR". If is was or, it was little problem. If you see the data structure I
sent you, Then I can try and put it forward in words,Then maybe it will be
easier to understand.
Search the name from table1
where object=s_tcode and field=tcd and value=f110
and object=F_REGU_BUK and field1=FBTCH and value1=02
and field2=BUKRS and value2=null
and object=F_REGU_KOA and field1=FBTCH and value1=02
and field2=KOART and value2=null
and object=F_REGU_BUK and field1=FBTCH and value1=03
field2=BUKRS and value2=null
and object=F_REGU_KOA and field1=FBTCH and value1=03
and field2=KOART and value2=null;

Can you try and help me now? Please.
Thanks for replying and taking care,
regards,
shivalee

Here if you see in the first line then f110 is attached to field tcd which
is inturn fixed to object s_tcode and so on for the rest of the rows.
If I use "AND" it is not attaching specific values to fields and fields to
objects.
If I use "OR" it is only executing the 1st found query giving results and
stopping, and not executing any rows after that.
I would wish to use "and" for inside each individual query as well as for
adding each query to each other to make a full compound query. BUT HOW? is
the main question here.
 
sorry ken:
this portion was supposed to come in the last:
Can you try and help me now? Please.
Thanks for replying and taking care,
regards,
shivalee

sorry for the mix up.
shivalee
 
So you want all values of LOW field so long as it's not empty?

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 (
Len(agr_1251.LOW & "") > 0))
OR
(((agr_1251.FIELD)="bukrs") AND ((agr_1251.LOW)=""))
OR
(((agr_1251.OBJECT)="f_regu_koa") AND ((agr_1251.FIELD)="fbtch") AND (
Len(agr_1251.LOW & "") > 0))
OR (((agr_1251.FIELD)="koart") AND ((agr_1251.LOW)=""));
 
I have applied an id to each component and my now query is:

SELECT sod.OBJCT, sod.FIELD1, sod.VALUE1, sod.FIELD2, sod.VALUE2
FROM sod
WHERE sodid=1;

can you now tell me how I can run it recursively, for "n" number of sodid's.
shivalee
 
I'm sorry... I don't understand what you mean in this context for
"recursively"? I am not making the connection to the previous posts in this
thread?
 
I have a file which has 200 sodids.the query I have written is for the 1st
sodid. how can I write a code whcih will pick each sodid one by one and run
this query?
can you suggest?
Shivalee
 
Are you sure you need to run each value independently? Are you sure a single
query that returns all records, sorted by the sodid value, won't work for
you? A report then would group the data for you.

Very difficult for me to recommend code as I don't have any idea of the
environment in which you'd run the code, where you want to put the output of
the query (I'm assuming that it isn't just to be displayed to the user on
the screen in a datasheet view), etc.

You are not giving us enough information so that we can make suggestions.
 
Back
Top