I'm not even sure how to put the subject line so I make sense. Here's the situation:
I'm creating a database and report for a fantasy character's magic spell circle.
The criteria:
The character learns words, nonsensical 6-letter words. Each word is a component of a spell. Some words are components of many spells, some only of one. Two words make up the "first" component of each of 2 different categories of spells: Attack/Combat Inhibitor, and Defense/Utility.
I have all the names of the spells and how many words go into each one.
I have the first word for every spell already.
I've given each word I have learned thus far a numerical value.
I've plugged into the "spell" table the numerical values of each "placement" of words that I know so far.
Example:
In the "words" table, the word convex is 43. It is a word that will fit into a 4th placement of some of the spells, and it's the 3rd "4th-word" word I have learned so far.
In the "spells" table, the spell Ebon Claw is a 2-word spell, beginning with cestir, and its second word will contain whichever word is assigned 24. I haven't learned the 4th "2nd-word" word yet, but when I do, it will be assigned #24.
I want the query to extract the correct words from the "word" table, and apply them to the numbers in the "spell" table, so that I can spit out a report when I'm finished. The report will tell the following:
The name of the spell.
The word combination of the spell.
The description of what the spell does.
It will be sorted by "type" of spell: Attack, Combat Inhibitor, Utility, and Defense.
My trouble right now is with the query. In really lousy pseudocode, this is what I've asked:
If the word table shows a word whose numerical value begins with 1
then
find that word in the spell table column Word1 and plug the word into the field that
has the matching value.
If the word table shows a word whose numerical value begins with 2
then
find that word in the spell table column Word2 and do the same.
Etc. etc. all the way to Word8.
But I can't figure out the syntax. Anyone have a suggestion?
I'm creating a database and report for a fantasy character's magic spell circle.
The criteria:
The character learns words, nonsensical 6-letter words. Each word is a component of a spell. Some words are components of many spells, some only of one. Two words make up the "first" component of each of 2 different categories of spells: Attack/Combat Inhibitor, and Defense/Utility.
I have all the names of the spells and how many words go into each one.
I have the first word for every spell already.
I've given each word I have learned thus far a numerical value.
I've plugged into the "spell" table the numerical values of each "placement" of words that I know so far.
Example:
In the "words" table, the word convex is 43. It is a word that will fit into a 4th placement of some of the spells, and it's the 3rd "4th-word" word I have learned so far.
In the "spells" table, the spell Ebon Claw is a 2-word spell, beginning with cestir, and its second word will contain whichever word is assigned 24. I haven't learned the 4th "2nd-word" word yet, but when I do, it will be assigned #24.
I want the query to extract the correct words from the "word" table, and apply them to the numbers in the "spell" table, so that I can spit out a report when I'm finished. The report will tell the following:
The name of the spell.
The word combination of the spell.
The description of what the spell does.
It will be sorted by "type" of spell: Attack, Combat Inhibitor, Utility, and Defense.
My trouble right now is with the query. In really lousy pseudocode, this is what I've asked:
If the word table shows a word whose numerical value begins with 1
then
find that word in the spell table column Word1 and plug the word into the field that
has the matching value.
If the word table shows a word whose numerical value begins with 2
then
find that word in the spell table column Word2 and do the same.
Etc. etc. all the way to Word8.
But I can't figure out the syntax. Anyone have a suggestion?