Re: SQL Query - "All Possible Combinations"

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

This should do the first bit:

SELECT tblWords.Word & tblwordsAgain.Word AS Result
FROM tblWords, tblWords AS tblWordsAgain
WHERE tblWords.Word<>tblwordsAgain.Word

Assuming your table is called tblWords and the field holding the words is
called Word. Bascially you don't join the instances of the table at all, and
then get rid of results where the words are equal.

I'm not too sure what you mean for the next bit. I can't understand why
you'd want to shove the results in a new table???

HTH
Sam
 
Thanks very much. I'm new to this so maybe I don't want to put them into a
new table. I really just want to be able to compile this "all possible
combinations" list one time (it's too big to do it every time) and be able
to compare another list that I provide (column "b") against it.

I really don't understand a lot of the concepts yet (I'm learning) but I
really need to complete this so that's why I'm asking in such a newbie
language.

Is there a way to compare column "a" with column "b" to produce the results
in the resultant query?

Thanks!
 
Hi Again,

I guess I should rephrase my question like this: I am looking to generate
all possible combinations of single words in column "a" and to end up in a
result1. I would then like to check to see if any of the terms in column "b"
match "Result1". If they do, display the final result.
*please excuse my lack of correct terms. I am using this example to explain
what I am looking to do from a conceptual basis only because of my
newbieness :-)
"a" "Result1" "b" "Final Result"
one onetwo tenfour twoone
two onethree ninetwo twothree
three twoone twoone
twothree twothree
threeone
threetwo

I am dealing with rather large set of numbers so I would like to keep the
processes down as much as possible.

Any help with the code would be a huge help.

Thanks very much.
 
If your "a" field is called Word and is in table tblWords and your "b" field
is called MultiWords and is in table tblMultiWords then the following SQL
should, I think, do waht you want.

SELECT tblWords.Word & tblWordsAgain.Word AS Result
FROM tblWords, tblWords AS tblWordsAgain, tblMultiWords
WHERE tblWords.Word & tblWordsAgain.Word=tblMultiWords.MultiWord And
tblWords.Word<>tblWordsAgain.Word

Just paste it into the SQL view of a new query, then alter the table and
field names to suit.

This query is not all that complex and should be quite efficient even if
you've got many thousands of records.

HTH
Sam
 
Hi Sam,
Thanks again for your help. Unfortunately I don't think this is what I am
looking for. It is asking me to "Enter Parameter Value" and says
"tblMultiWords.MultiWord: when I run the query. Can't it run all of "b"
against all of "result1" or do I have to input each phrase manually? Maybe I
just messed something up?

Thanks very much!
 
Never, I think all I had to do was switch my "b" name from MultiWords to
MultiWord. It is running right now so I think it is working.

Thanks again!
 
Back
Top