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.