I need some help with getting totals

[JennW]

I have a table that looks something like this....

Country Silver Gold Bronze Total

USA 3
USA 7
Russia 3
Canada 3
Russia 3
Canada 3


What I need is to create a total in the "Total" column that groups the countried together and adds the numbers that total for that country. In effect, I need this data to show up like this....

Country Silver Gold Bronze Total
USA 3 7 10
Russia 3 3 6
Canada 3 3 6


I have tried multiple queries and I am getting nowhere.

Any suggestions?

thanks!

JennW

 

[papparuff]

You need to redesign your table to look like this:

Country - Data Type: Text (PRIMARYKEY)
Gold - Data Type: Number
Silver - Data Type:Number
Bronze - Data Type:Number

Save the table then enter the country with their total Gold, Silver, and Bronze medals.

Here is what the SQL statement looks like to total everything:

SELECT DISTINCTROW tblOlympic_Medals.strCountry, Sum(tblOlympic_Medals.Gold) AS [Total Gold], Sum(tblOlympic_Medals.Silver) AS [Total Silver], Sum(tblOlympic_Medals.Bronze) AS [Total Bronze]
FROM tblOlympic_Medals
GROUP BY tblOlympic_Medals.strCountry;
John Ruff - The Eternal Optimist

 

[KyleS]

John, the SQL looks great, (it's the same SQL I would have given) but I don't think Jenn needs to recreate her table. The SQL will work with the table setup she has now...

Kyle :

 

[JennW]

John,

thanks, and I agree, it needs to be redesigned. However, there are already over thousands of records in this database that are seperated out this way. So now I have to figure out how to get the totals I need with the existing format.

Is it even possible to get these totals?

JennW

 

[KyleS]

Jenn, this is thrown together, so the names aren't quite right (replace "table2" with your table name, etc), but this will give you EXACTLY what you're looking for...

SELECT Table2.Country, Sum(Table2.[Gold]) AS SumOfGold, Sum(Table2.[Silver]) AS SumOfSilver, Sum(Table2.[Bronze]) AS SumOfBronze, Sum([table2].[Gold])+Sum([table2].[Silver])+Sum([table2].[Bronze]) AS Total
FROM Table2
GROUP BY Table2.Country;

Kyle :

 

[mac318]

Jenn, I may be missing the boat; but, it appears that you simply need a form with one group; that is, group by Country. In the Group footer, use unbound text boxes with the Sum function; i.e.;
=Sum(Bronze)
=Sum(Silver)
= Sum(Bronze)+ Sum(Silver) + Sum.....

Place a copy of each text box with its code in the form footer to get a total of all medals issued.

mac

 

[JennW]

Kyle,

your statements worked wonderfully with one exception. If the country only has one or two columns with numbers in them, the total is empty. How can I get it to count ALL numbers even if there is an empty field?

Thanks for your help!!!!

JennW

 

[GIA]

How about IIf(IsNull(foo),0,foo) "Outside of a dog, a book is probably man's best friend; and inside a dog, it's too dark to read" - G. Marx

 

[Guest_imported]

Sorry Jenn, here is the correct SQL statement that will account for nulls:

SELECT Table2.Country, Sum(Table2.Gold) AS SumOfGold, Sum(Table2.Silver) AS SumOfSilver, Sum(Table2.Bronze) AS SumOfBronze, IIf(IsNull(Sum([table2].[Gold])),0,Sum([table2].[Gold]))+IIf(IsNull(Sum([table2].[Silver])),0,Sum([table2].[Silver]))+IIf(IsNull(Sum([table2].[Bronze])),0,Sum([table2].[Bronze])) AS Total
FROM Table2
GROUP BY Table2.Country;

Let me know if I can be of further assistance
Kyle :

PS, I just changed my name "AccessIsFun" was more of a sarcastic "joke", so I changed it just now ;-)

 

[JennW]

Kyle, (and everyone else that contributed)

thank you soooo much! You just saved me hours of work and frustration. Did I ever tell you your my hero?

thanks again!

JennW