Pulling only one medal for a team in Olympic database

[Kahlia]

Hi. Now to start with I'm not asking you to provide me with a solid answer to this, but enough information so that I know where to start looking would be good.

I have a project that I am working on which is an Olympic Database. I have several tables connected with 1:M relationships. The question I am working on now is how to pull from these tables the number of Gold, Silver and Bronze medals won by each country (separately) and the then do a score calculation on them. I have worked out how to calculate the numbers of Gold, Silver and Bronze, but wish to only count the number of medals for a team as 1 (for the country) and at present am pulling in all medals won by the team as individuals.

The Medal table is in format: Medal_id, Medal_Color, Cont_id - where cont_id is the contestant id and relates it to the contestant table.

The Contestant table is in the format: Cont_id, Rep_id, Comp_id, Team_id - where Rep_id is the id of the representative, comp_id the id of the competition and team_id the id of the team.

I want to write the query in SQL that Access 2003 supports and am not sure where to begin with making the count query not count the medals won by a contestant with the same team_id as another contestant.

My other problem / issue is that I would like the count queries that I do to show the medal winners show zeros where they encounter NULL values.

Any help anyone can provide would be greatly appreciated.

[Loony064]

Hey there

From what i understand, you have a table that stores medals won by various contestants. Various contestants come from various coiuntries. You want to know how many medals in total each country has won.

The first thing i would do is generate a distinct list of those contestants who actually won a medal.

Secondly, i would then determine which countries each of these medal winning contestants belonged to.

Now that you know who has won a medal and what country they belong to, you can look back at the table of "medals won". Look at one country at a time. For each contestant who competes under that country, look at the "medals won" table. In that table, find the corresponding contestant and add the medals won to the total for the country. Repeat the process for each country.

I'm sure there is a more refined process to accomplish this task, but hopefully this will give you a starting point which can help you develop the logical process needed to accomplish this task.

good luck
Laura