select query on array field

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

Shivalee Gupta via AccessMonster.com

i am working with access 2000. i am a total new person in this field.
i need to store a field that has many types of values. example:
i have a code abcd. it has an object stcode, which has a field tcd and
which has values f110. this will form one group, inside abcd.
inside abcd, is also another object f_regu. which has field fbtch, which
has values 2,3,11. these values 2,3,11 should be attched to this filed
fbtch, for this group. then there is another field inside f_regu itself,
bukrs, which has no value.
please try and imagine a tree-like structure.
then i need to put in a query, that states that :
search for that code, which satisfies the above 2 rules, with group one and
group two.
please...please help me.
Thanks in advance,
regards,
shivalee
 
Shivalee

Isn't this the same question you posted later (see later response).

Jeff Boyce
<Access MVP>
 
dear jeff,
i am sorry about my posting the question thrice. i shall not do it again.
please answer my question now. i have reduced the rating of my other two
questions to 1 so as they can be taken off. so only this site is working.
please answer here.
regards,
shivalee
 
Shivalee

I can see now, in re-reading your post, that it is related, but not the same
as your later post. If I understand your situation, you are trying to embed
multiple values in a single field. This is frowned on in relational
database (i.e., Access) design.

If you have a record that can have 0, 1, or "many" values related to it, you
have described a "parent-child" relationship, requiring two tables, and
related 1:m. Could you post an idea of your table structure, perhaps
something like:

tblTable1
Table1_ID
Field1
Field2

and a description of the kinds of value(s) in the fields?
 
first of all, thanks for replying. second, i dont think i need to make a
table. i already have a table with those contents. what i need is to query
from my table. my table looks like this:
col1- id
col2- object
col3-field
col4-value
for 1 id, i have 2-3 objects, for each object i have 2-3 fields and finally
for each field i may or may not have value/values.
then i need to make the query, that for this group search the name attched
to the whole group.
for example, the query's english would be like this:

object s_tcode having field tcd having value f110 AND
object f_regu_buk having field fbtch having values 2,3,11,21,25 and also
field bukrs having value nothing AND another object f_gu_koa having field
fbtch having values 2,3,11,21,25 and another field koart having value
nothing.
i think i am clearer this time?
thanks for replying,
shivalee
 
Sorry for the delay ... I've been out of town.

As I am interpreting your description, your data structure has multiple
values embedded in a single field. This is counter-productive in a
relational data design.

Perhaps I am misunderstanding. Could you also provide examples of the data
you have in your structure?
 
thanks for replying jeff. here is what I have:
1st column is sodid, number
2nd column is id, text
3rd column is object, text
4rth column is field, text
5th column is value, text.

for the above structure, I had made 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.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)=""));

If you copy this SQL query in a new query and then see the design, you will
probably know what I need.
If still you are unable to understand, and If still you wish to help me,
then can you send your e-mail id, i'll send the file over for explaination.
thanks for replying,
shivalee
 
Shivalee

I may be misinterpreting your SQL, so you may wish to re-post your question
to the group to get others to consider it...

What I believe I noticed was:
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"))
Click to expand...

that you are searching for the value of agr_1251.LOW to be 2 AND 3 AND 11
AND 21 AND 25, all at the same time. If you have designed your database to
hold one fact in one field, the value could only be ONE of these, not all of
them.

If you have designed your database to hold multiple values in a single
field, you would need to use the "Like" operator and wildcards to specify
that the multi-value field had to contain (something):
Like *2* AND Like *3* AND Like ...
(your actual syntax may vary).
 
You are very helpful and very kind. Not only this but you are also very
correct. I have also studied and re-studied my query and I feel either it
is wrong or it is not giving me the required results. Actually,the moment I
write this line in my query:
(((agr_1251.OBJECT)="s_tcode") AND ((agr_1251.FIELD)="tcd") AND (
(agr_1251.LOW)="f110"))
After this line is a word "OR". the moment the query encounters this OR,it
does not execute the rest of the query.This is issue no.1 & the issue no.2
is what you are talking about. Inside a part of the query, it states:
((agr_1251.LOW)="2" And (agr_1251.LOW)="3" And (agr_1251.LOW)="11" And
(agr_1251.LOW)="21" And (agr_1251.LOW)="25"))
Here also the issue is what you are talking about. low can have at any
point of time 1 value. here i am showing 5 values.
If I have to run this query the way I want to, can you specify an
alternative, Please!
Thanks ever so much,
shivalee
 
Shivalee

If "low" can only hold one value in one record, then I believe you are
trying to find records in which the value of "low" is:
((agr_1251.LOW)="2" OR (agr_1251.LOW)="3" OR (agr_1251.LOW)="11" OR
(agr_1251.LOW)="21" OR (agr_1251.LOW)="25")

I would suggest stepping back from the much more complex issue of using
UNION and getting each individual query to work.

Also, if you are working directly in SQL, another approach would be to use
the Access query design mode. Select fields and indicate criteria in query
design mode first, until the query is returning the rows you expect. Then
inspect the SQL statement that underlies the query.
 
I have actually done that, stepping down and using design mode. I have also
tried SQL coding and all. But all in vain.
Eventually I need these 2 things: 1) I need the low having "ALL" the
values and 2) I need all the 5 parts of the query to give me one combined
result, i.e. a name which adheres to all the 5 queries seperately as well
as each query individually. ( I think that is huge!)
Probably, if it is not possible using access, I could then maybe use code
or something. But the criteria is the same, that I am sure.
Thanks a ton,
shivalee
 
Shivalee

Please take another look at my response up-thread. If you have a field that
contains more than one value (in a single record's field), you will have to
use the "Like" operator. However, if that is true, you have not designed
your data structure to make best use of Access' tools and strengths, and
will have to work very hard to come up with work arounds.

To recap, if your data in a single row's field looks like:
2, 3, 4, 5, 6, 11, 12, 20, 21, 22, ...

and you wish to find a row for which this single field hold either a "2", or
a "12", or a "22", ..., you will need to spell each of those out using the
"Like" operator and wildcards, and using the "OR" operator. Or, if you only
want those where ALL values are present in the single row's field, use the
"AND" operator.

Next point -- your SQL statement tested for "2" (among other values). "2"
is considered by Access to represent a text value. Is your field of type
text or type numeric?

But I keep coming back to the likelihood that you've tried to put multiple
values in a single row's field -- if so, you (and Access) will have
headaches doing what you've described. There is an alternative approach to
this, which I mentioned up-thread -- normalize the data further.
 
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
 
Back
Top