Sum a value based off multiple fields

[kit_sune]

I'm trying to build a query that uses the information pulled from controls on a form called "CharacterCreation" - 2 of the controls are "Race" and "Class", and the form stores this data in a "Characters" table.
The query needs to refer to the Characters table, and another table called "Modifiers".
In this second table I have several numerical fields such as hp, str, etc, and I have two other fields called "Type_Modifier" and "Type"
Among the Type_Modifiers are Race and Class, where their corresponding "Type" could be for instance Demon and Magician..

So the idea is that when I mark on the form (and thus creating the record in the first table) a Race of "Demon", and a class of "Magician" I want the query to refer to both of these fields, and sum the values in the second table where the "Type" is either "Demon" or "Magician" or whatever I decided to choose. I feel like I should be able to figure this out but I'm having a hard time...

Here's an example of the SQL I've tried that shows nothing.

SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, Modifiers.Type
HAVING (((Modifiers.Type)=[Characters].[Race]));

I also tried this, which I thought would work but it displays nothing as well.

SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, [Type]=[Characters].[Race];

 

[plog]

Without sample data i can only tell you what looks funny in your SQL. Here's what I see:

(Modifiers.Type_Modifier = Characters.Class) AND (Modifiers.Type_Modifier = Characters.Race)
WHERE (((Modifiers.Type)=[Characters].[Race]))

You've essentially established 3 different links between your table, even though 2 are in the JOIN and 1 is in the WHERE clause, they are effectively all JOINs. That's not inherently bad, but the fields you've used to do so is odd.

Using simple logic operations, your JOIN will only show records where these conditions are true:

1. Modifiers.Modifier_Type = Modifier.Type
2. Characters.Class = Characters.Race

Did you mean for that to be the case?

Again, it would be better if you provided sample data from both your tables and then what results your query should produce from that sample data--along with all relevant field and table names.

 

[kit_sune]

I suppose I can do that. I was trying not to show too much because I was slightly embarrassed that I was using Super Mario references, but I guess I can use the disclaimer that I'm just using things I'm familiar with to help me learn this stuff!

Here's a piece of the tables and I'm hoping they display properly:

[FONT=Calibri][FONT=Courier New][SIZE=3]Type_Modifier,Type,  hp[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Species, Magi Koopa, 5[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Species, Royal Koopa,9[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Species, Yoshi,      7[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Race,    Spotted,    10[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Race,    Striped,    8[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Race,    Solid,      6[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Color,   Green,      9[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Color,   Purple,     6[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Color,   Blue,       3[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Class,   Fighter,    2[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Class,   Magician,   0[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Class,   Rogue,      4[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=3]ID,Char_Name, Player_Name, Species,     Race,   Color1, Color2, Class[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]1, Hypshi,    Kit_sune,    Yoshi,       Solid,  Purple, N/A, Magician[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]2, Bowser Jr, Joe_shmoe,   Royal Koopa, Spotted, green,  red, Fighter[/SIZE][/FONT]
[/FONT]

As I said before the idea at this point is to get the HP for Yoshi, Solid, and Purple to add up for the first character...

 

[plog]

I can't parse your data. Seperate it by commas not spaces. Also, provide me what data your query should return based on the sample data you provided.

 

[kit_sune]

I updated my previous post to show comma delimited text as requested.

Pat I'll give your suggestion a try. It can't hurt at this point, it's 11pm here so I don't even know what I'm doing at the moment! lol

Also, I've realized since I first posted that I was using Type_Modifier when I should have been using just Type - this didn't fix the issue but I'm sure its a start.

 

[kit_sune]

I've learned that if I try each value individually it will pull them but when I try to pull multiple at once it won't do it....

 

[plog]

Last chance:

Also, provide me what data your query should return based on the sample data you provided

 

[kit_sune]

My apologies, thanks for your patience.


Using the tables above I'm trying to have the query (to start with) show the Character's name, and a sum of the hp values based on what race, species, class, color1, and color2 was selected. I'm starting with hp but of course I have more, once we establish what the format should look like I'm sure I can carry on from there...

Thanks again,

~Kit.

 

[kit_sune]

So in other words the two examples should be as follows:

Char_Name, hp
[FONT=Courier New][SIZE=3]Hypshi,   19[/SIZE][/FONT]
[FONT=Courier New][SIZE=3]Bowser Jr,30 (since I didn't provide a red color modifier)[/SIZE][/FONT]

Edit:

I figured it out - by removing the relational link I was able to get it to work perfectly.

Here's how the SQL looks:

SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp, Sum(Modifiers.cp) AS SumOfcp, Sum(Modifiers.fp) AS SumOffp, Sum(Modifiers.str) AS SumOfstr, Sum(Modifiers.def) AS SumOfdef, Sum(Modifiers.wis) AS SumOfwis, Sum(Modifiers.int) AS SumOfint, Sum(Modifiers.agl) AS SumOfagl, Sum(Modifiers.spd) AS SumOfspd, Sum(Modifiers.stm) AS SumOfstm
FROM Modifiers, Characters
WHERE (((Modifiers.Type)=[Characters].[Species] Or (Modifiers.Type)=[Characters].[Race] Or (Modifiers.Type)=[Characters].[Class] Or (Modifiers.Type)=[Characters].[Color1] Or (Modifiers.Type)=[Characters].[Color2]))
GROUP BY Characters.Char_Name;

Thanks for helping me brainstorm through it, it really is appreciated.

~Kit

 

[kit_sune]

What you have ended up with is a Cartesian Product. This takes all the rows in tblA and matches them with every row in tblB. So, if tblA has 1,000 rows and tblB has 500 rows, your result set has 500,000!!!! rows. Then your criteria is applied to whittle down the results.
(shortened)
The advantage of a normalized schema is that you can add additional attributes by simply adding rows in tblAttributes and all your queries, forms, etc still work with NO changes. So adding "strength" to your current schema means changing a table to add the column and then all the queries, forms, and reports to show it.

Cool thanks for the info, I'll look into applying your suggestion. I appreciate your taking the time to help me out.

~Kit

 

[kit_sune]

I don’t really like having an id field on two of my tables (tblStatValue and tblAttributeValue) are they necessary?
Also, I can understand why it would be beneficial to load all the attributes into a single column, rather than create a separate column for each one, but it seems strange to me. I had initially thought it would be best to create a single record for each character, doing it this way causes there to be a larger amount of records than I had anticipated. It’s getting a little confusing for me but I really want to figure this out.
I’ve attached my database, please take a look and see if you can offer any suggestions.
As said previously, the idea of this database is to simply allow a user to create a “character” based off the “tbleType…” tables (Which hold the attribute data at this time, with eventual description fields) and a character creation form. And then from there I was going to have a secondary form that utilized a tab layout to lookup data about that character, from its statistics (hp – health, fp – flower points, cp - color power, and so on), while the statistics were based off a summation of the attributes that the user chose.
~Kit
ps,
I have a newer laptop but instead of winzip it had a program called 7zip. It saved the file as a .7z ... so I changed the extension to .zip and uploaded it. It still allowed me to open it from here but ymmv...