Calculations on Groups (1 Viewer)

[$t3ff]

tblCountries -- CountryID, Country
tblAthletes -- AthletesID, FirstName, Surname, CountryID, M/F
tblSports -- SportID, Sport
tblEvents -- EventID, Event, VenueID, SportID
tblVenues -- VenueID, Venue, Location
tblMedals -- MedalID, MedalType
tblResults -- ResultID, AthleteID, MedalID, EventID

Primary Keys - Bold
Foreign Keys - Italic

This is the tables in my database, it is a database of the upcoming Beijing Olympics.
I am aiming to have a medal count for each country which is in the current database.
As it has the capabilities to expand therefore I am trying to avoid the need to create a whole new query for each country I add, also it would be preferable to be able to have all of the countries on the same report, so that I can compare the totals.
Is there some way I can count the total of each group of records (each country) for the field where 'MedalType' = "Gold", "Silver" and "Bronze". Also the calculation needs to be done before the report so that I can order by the total.
I am new to this so can answers be preferably in layman's terms.

 

[Dennisk]

use a total query and group by country. you could also group by country and by medal type. Then you need a count so just count up the primary keys.

 

[$t3ff]

How in the count, can i make it have seperate counts for each country?

 

[jazi]

Sums and expressions maybe?

make one column for country, next for gold, next for silver and last for bronzein a table. Than make a query, include all columns. Hit the total button in design view for query(big E). New fields will now show up in the columns (referred to as groupby's) The country column should be "group by" and all 3 others should be "sum" Lastly, create one last column in the query itself (just put your cursor in the next empty column), you will have to type it in by hand. In the first field of the new column, type in "expr1: SumofGold + Sum ofSilver + SumofBronze", also change the "group by" to "expression". Hope that helps some.

 

[$t3ff]

Thankyou jazi for your suggestion, only probem being i have one field for medal (in which i write Gold, Silver or Bronze, as i could not link one field to 3 in the results table) I did eventually find a solution for anyone who needs it.

Field____MedalID____CountryID___Country______MedalID
Table___tblResults___tblAthletes__tblCountries___tblResults
Total____Group by___Group by____Count_______Where
Sort______________________________________Accending

This shows the total Gold, Silver and Bronze for each country, the total count will be done in a report.