Group data totals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm trying to take a query that pulls records out of a character table based
off of a form that contains two player names. This in turn pulls all their
known and specified relevant stats, and then adds up their numerical values.
And then gives me the below query output:

Character_Name.....Character_ID.....Stat_Total
Big_Bob........................................1.............33
Steve............................................6.............12
steve............................................6.............44

The SQL to get the above is:

SELECT Pqry_MOCC_Skills_to_Character.Character_Name,
Pqry_MOCC_Skills_to_Character.Character_ID,
[str_lvl]+[con_lvl]+[int_lvl]+[wis_lvl]+[dex_lvl]+[qui_lvl] AS stat_total
FROM Pqry_MOCC_Skills_to_Character
WHERE
(((Pqry_MOCC_Skills_to_Character.Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_1]
Or
(Pqry_MOCC_Skills_to_Character.Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_2])
AND ((Pqry_MOCC_Skills_to_Character.Skills_ID)=73 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=11 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=17 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=58 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=80 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=83 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=99));


What I would like to see is:

Character_Name.....Character_ID.....Stat_Total
Big_Bob....................................1.............33
Steve........................................6.............56

And if possible what I would really like to see is something that would
simply tell me who has the highest, like this:

Character_Name.....Character_ID.....Stat_Total
Steve...........................................6.............56


What's the best way about doing this... ?


Thank you anyone that can help! =)
 
Senexis said:
I'm trying to take a query that pulls records out of a character table based
off of a form that contains two player names. This in turn pulls all their
known and specified relevant stats, and then adds up their numerical values.
And then gives me the below query output:

Character_Name.....Character_ID.....Stat_Total
Big_Bob........................................1.............33
Steve............................................6.............12
steve............................................6.............44

The SQL to get the above is:

SELECT Pqry_MOCC_Skills_to_Character.Character_Name,
Pqry_MOCC_Skills_to_Character.Character_ID,
[str_lvl]+[con_lvl]+[int_lvl]+[wis_lvl]+[dex_lvl]+[qui_lvl] AS stat_total
FROM Pqry_MOCC_Skills_to_Character
WHERE
(((Pqry_MOCC_Skills_to_Character.Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_1]
Or
(Pqry_MOCC_Skills_to_Character.Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_2])
AND ((Pqry_MOCC_Skills_to_Character.Skills_ID)=73 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=11 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=17 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=58 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=80 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=83 Or
(Pqry_MOCC_Skills_to_Character.Skills_ID)=99));


What I would like to see is:

Character_Name.....Character_ID.....Stat_Total
Big_Bob....................................1.............33
Steve........................................6.............56

And if possible what I would really like to see is something that would
simply tell me who has the highest, like this:

Character_Name.....Character_ID.....Stat_Total
Steve...........................................6.............56
Click to expand...

To get the total, just use a GROUP BY clause so you can use
the Sum function:

SELECT Character_Name, Character_ID,

Sum([str_lvl]+[con_lvl]+[int_lvl]+[wis_lvl]+[dex_lvl]+[qui_lvl])
AS stat_total
FROM Pqry_MOCC_Skills_to_Character
WHERE

((Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_1])
Or

(Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_2]))
AND (Skills_ID IN (73.11,17,58,80,83,99))
GROUP BY Character_Name, Character_ID

To get just the highest one:

SELECT TOP 1 Character_Name, Character_ID,

Sum([str_lvl]+[con_lvl]+[int_lvl]+[wis_lvl]+[dex_lvl]+[qui_lvl])
AS stat_total
FROM Pqry_MOCC_Skills_to_Character
WHERE

((Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_1])
Or

(Character_Name)=[Forms]![frm_MOCC_Character_VS_INQ]![character_2]))
AND (Skills_ID IN (73.11,17,58,80,83,99))
GROUP BY Character_Name, Character_ID
ORDER BY

Sum([str_lvl]+[con_lvl]+[int_lvl]+[wis_lvl]+[dex_lvl]+[qui_lvl])
DESC
 
Back
Top