×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help with generating a table of reactions to test
2

Help with generating a table of reactions to test

Help with generating a table of reactions to test

(OP)
I have a list of elements.  For this, let's say they are Fire, Water, Earth, and Air.  Each of these elements can be combined in a way such that the *may* react and will produce something new.  This is inclusive, or it includes repeats.  So Air + Air = Wind, for example, and Air + Earth = Dust, and so forth.

So I have a table called 'Elements', and I have two columns in the table, an ID which is the Primary Key, and ElementName, which is unique.  I suppose I could just use the ElementName as the primary key, but whatever.

I want to be able to generate a list of reactions to test.  As I test them I'll update another table with the results of the reactions.  If a reaction produces a new element, I'll add that to the 'Elements' table, and generate a new list of reactions to test.

I hope this makes sense so far!

So I generated three queries, two of them did a SELECT DISTINCT from the original table to produce a list of all unique elements, and the third query performed a query on the first two:

Query3
SELECT *
FROM Query1, Query2
WHERE (((Query1.ElementName)<>[Query2].[ElementName]));


But this produces a table of 12 reactions to try.  The reason is because it gives me Air + Water as a reaction, and Water + Air as a reaction.  In this case, this is the same reaction and need not be repeated.

So I know I'm totally doing this wrong.  There must be an easier way to do this.

I originally looked at this as a math problem and found this information on Wikipedia: http://en.wikipedia.org/wiki/Combination#Number_of_combinations_with_repetition  The query should produce a set of 10 reactions to try, 10 combinations, from the original 4 elements.

I know a bit about queries but it's been a long time since I had a project like this, so don't assume I know too much!  :)

Thank you for your help!!

Thanks!!


Matt

RE: Help with generating a table of reactions to test

(OP)
Thanks for the suggestion.

When I do that, I get a total of 6 reactions to test.  The ones I'm missing are Air + Air, Earth + Earth, etc.

If I add <=, I get the 10 reactions.  Fantastic!

Thank you!

Thanks!!


Matt

RE: Help with generating a table of reactions to test

(OP)
OK, so here's my next problem.  I want to generate/update/append to a results table so that I have a report of what I've tried, and what I haven't tried.

I created a new table called 'Results'.  I want to update the Results table with all the new combinations.

The update query (Query3) looks like this:

CODE

INSERT INTO Results ( Reagent1, Reagent2, UniqueCombo )
SELECT Query1.ElementName, Query2.ElementName, [Query1].[ElementName]+[Query2].[ELementName] AS Expr1
FROM Query1, Query2
WHERE (((Query1.ElementName)<=[Query2].[ElementName]))
ORDER BY Query1.ElementName;

The "UniqueCombo" ensures that I don't append reactions that are already in the database, to prevent duplicates.

Problem is, as soon as the query sees duplicates, it stops and won't add the records.

So I'm guessing I need a fourth query that looks at Query3 and the Results table, but I'm not sure about the kind of "join" to use.  I need only the records from Query3 that don't match the Results table.

Suggestions?

Thanks!!


Matt

RE: Help with generating a table of reactions to test

(OP)
Thank you!  I started a somewhat duplicate thread in the Access forum and someone posted something similar.

Thanks!!


Matt

RE: Help with generating a table of reactions to test

Just a note, when using NOT IN (SELECT ...) , beware of NULL's in the sub-select.
Example:

SQL>create table t1 (c1 integer primary key, c2 varchar(10));
SQL>create table t2 (c1 integer);
SQL>insert into t1 values (1,'Alfa');
SQL>insert into t1 values (2,'Bravo');
SQL>insert into t1 values (3,'Charlie');
SQL>insert into t2 values (2);          
SQL>select * from t1 where c1 not in (select c1 from t2);
         c1 c2
=========== ==========
          1 Alfa
          3 Charlie

                  2 rows found

SQL>insert into t2 values (null);
SQL>select * from t1 where c1 not in (select c1 from t2);

                  0 rows found

SQL>select * from t1 where c1 not in (select c1 from t2 where c1 is not null);
         c1 c2
=========== ==========
          1 Alfa
          3 Charlie

                  2 rows found

 

RE: Help with generating a table of reactions to test

(OP)
Interesting.  I will review this.

I used to be so much more comfortable with SQL, but I haven't touched it for 7 years...

Thanks!!


Matt

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close