Hello:

I have table "aspects" that contains only fields "ID" (numeric KEY) and
"aspect" (char string) such as:
1 rough
2 smooth
3 shiney
4 dull
5 wet
6 dry
7 hot
8 cold
....
25 "" (empty string)

In table "items", there are numerous fields, but the ones of concern are 5
fields "aspect" (numeric)
given a number indicating DECREASING importance, as in:
items.aspect1
items.aspect2
items.aspect3
items.aspect4
items.aspect5

The intent is to have each of aspect1 thru aspect5 populated with a value
from table "aspects" (or just aspect1, or just aspect1 & aspect2, etc...).
Since items.aspectX is a numeric as is aspects.ID, I know there has to be
some sort of lookup into table aspects FOR EACH of 5 items.aspectX fields.
I plan to use a form where the user enters 1 to 5 aspects in desired
weighting order, then the query returns appropriate rows. The understanding
is if the user enters 2 aspects (such as shiney, hot) , the remaining 3 must
be "" (empty string).

Using INGRES SQL (I used to know) bastardized with Access forms, a query
might look like:

select
i.*
from table
items i
where
i.aspect1 = (select a1.ID from aspects a1 where a1.aspect =
Forms!lookup!aspect_string1) AND
i.aspect2 = (select a2.ID from aspects a2 where a2.aspect =
Forms!lookup!aspect_string2) AND
i.aspect3 = (select a3.ID from aspects a3 where a3.aspect =
Forms!lookup!aspect_string3) AND
i.aspect4 = (select a4.ID from aspects a4 where a4.aspect =
Forms!lookup!aspect_string4) AND
i.aspect5 = (select a5.ID from aspects a5 where a5.aspect =
Forms!lookup!aspect_string5) AND;

From what I remember of SQL "cursors", 5 cursors would be required to allow
the 5-level lookup, thus each is a subselect. But, I'm pretty sure the
above SQL is the wrong syntax, even if the intent is right. I don't think
this can be done with a 2-table join, because 5 seperate lookups are needed.
So, the following wouldn't work:

select
i.*
from table
items i, aspects a
where
i.aspect1 = a.ID AND a.aspect = Forms!lookup!aspect_string1 AND
i.aspect2 = a.ID AND a.aspect = Forms!lookup!aspect_string2 AND
i.aspect3 = a.ID AND a.aspect = Forms!lookup!aspect_string3 AND
i.aspect4 = a.ID AND a.aspect = Forms!lookup!aspect_string4 AND
i.aspect5 = a.ID AND a.aspect = Forms!lookup!aspect_string5;

I have been pouring over the INNER JOIN, LEFT/RIGHT OUTER JOINS in Access 97
to see if this can be done. Any suggestions?

Thanks much.
Bob
rjm (AT) soltec (DOT) net




-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Im only slightly confused by your post, but I have a suggestion
anyway... Have you considered doing this in code? VBA would allow you
to build the query dynamically, assinging it to a string, and then
execute it when the user hits a submit button, or as each selection is
made. for instance if you had a series of 5 dropdowns on your form
populated with the possible values (and their foriegn key ids) then as
they selected the first value you would initialize strSQL and
concatenate values for your WHERE clause, allowing you to build the
exact logic you need in your query to fulfill their request.

If that makes sense for you let me know and I'll be glad to help work
it out for you.

John
jobrien AT acscience DOT com
(do not use the assigned email for this acct)


"RJM" <rjm (AT) soltec (DOT) net> wrote