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
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!!
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
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
Talk To Other Members
RE: Help with generating a table of reactions to test
SELECT *
FROM Query1, Query2
WHERE Query1.ElementName<Query2.ElementName
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Help with generating a table of reactions to test
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
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
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
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Help with generating a table of reactions to test
Thanks!!
Matt
RE: Help with generating a table of reactions to test
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
I used to be so much more comfortable with SQL, but I haven't touched it for 7 years...
Thanks!!
Matt