Counting the number of matching items in a query

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

Hey guys,

I am trying to create a baseball league database for a league I run. I have
run into a problem as I cannot find the right command to count the number of
times in the Score Query that a specific team has won.

I have 1 table that I am using for the team names and details
I have 1 table that I am using for the schedule and scores with a lookup
relationship to the team name table.
I am using a query on the schedule and scores and calculates the winning
team and losing team based on the scores.

I need to figure out how to run a query or report that will count the number
of times a specific team is listed in the Winner section and loser section so
I can create a standings list.

I think I am looking how to build the expression to count the number of
times winner = team name and output that value..

this is the formula I am work with in my report :
=Sum(Abs("[2009 Schedule and Score Calculation]![WinnerTest]"="[Team Name]"))

Please find the links to two pictures of my database to help out.

http://s82.photobucket.com/albums/j...iew&current=CompleteScheduleReportwscores.jpg

http://s82.photobucket.com/albums/j...ew&current=ScheduleScoresAddandEditScreen.jpg
 
Sorry about the links.. Was just trying to give as much information as
possible upfront as I have been stuck on this for a week now trying different
commands etc. lol

Table: Team Table
Team Name - Text - Primary Key
Previous Name - Text
Division - Text

Table: 2009 Schedule/Scores
Game Number - Autonumber - Primary Key
Date - Date/Time - Date of game on schedule
Home Team - Text - A linked lookup to Team table/Team Name
Away Team - Text - A linked lookup to Team table/Team Name
Park - Number
Diamond - Text - A linked lookup to Park List Table/Park
Time - Text - Lookup list of pre-defined times
Home Score - Number - Score of the home team after game was completed
Away Score - Number - Score of the away team after game was completed
Completed - Yes/No - Check box showing game was completed

Query: 2009 Schedule and Score Calculation
SQL View:
SELECT [2009 Schedule/Scores].[Game Number], [2009 Schedule/Scores].[Home
Team], [2009 Schedule/Scores].[Away Team], [2009 Schedule/Scores].[Home
Score], [2009 Schedule/Scores].[Away Score], IIf([Completed]=True And [Home
Score]>[Away Score],[Home Team],IIf([Completed]=True,[Away Team])) AS
WinnerTest, IIf([Completed]=True And [Home Score]<[Away Score],[Home
Team],IIf([Completed]=True,[Away Team])) AS LoserTest, IIf([Home
Team]=[WinnerTest] And [Completed]=True,+2,IIf([Completed]=True,-1,0)) AS
Hpoints, IIf([Away Team]=[WinnerTest] And
[Completed]=True,+2,IIf([Completed]=True,-1,0)) AS aPoints, [2009
Schedule/Scores].Completed, [2009 Schedule/Scores].Date, [2009
Schedule/Scores].Park, [2009 Schedule/Scores].Diamond, [2009
Schedule/Scores].Time
FROM [2009 Schedule/Scores];


I am trying to run either a report or Query to count the number of times a
teams name shows up in WinnerTest and LoserTest which will give me an output
either on the report or Query like:

Team Name Wins Loses
NutZ N Boltz 2 1

Hope this helps.

Thanks

Jared


Jared said:
Hey guys,

I am trying to create a baseball league database for a league I run. I have
run into a problem as I cannot find the right command to count the number of
times in the Score Query that a specific team has won.

I have 1 table that I am using for the team names and details
I have 1 table that I am using for the schedule and scores with a lookup
relationship to the team name table.
I am using a query on the schedule and scores and calculates the winning
team and losing team based on the scores.

I need to figure out how to run a query or report that will count the number
of times a specific team is listed in the Winner section and loser section so
I can create a standings list.

I think I am looking how to build the expression to count the number of
times winner = team name and output that value..

this is the formula I am work with in my report :
=Sum(Abs("[2009 Schedule and Score Calculation]![WinnerTest]"="[Team Name]"))

Please find the links to two pictures of my database to help out.

http://s82.photobucket.com/albums/j...iew&current=CompleteScheduleReportwscores.jpg

http://s82.photobucket.com/albums/j...ew&current=ScheduleScoresAddandEditScreen.jpg
Click to expand...
 
I would start by creating a normalizing union query that can be the source
for a totals query:

SELECT [Game Number], [Date],
[Home Team] As Winner, [Home Score] as WinnerScore,
[Away Team] As Loser, [Away Score] as LoserScore
FROM [2009 Schedule/Scores]
WHERE Nz([Home Score],0) > Nz([Away Score],0)
UNION ALL
SELECT [Game Number], [Date],
[Away Team], [Away Score],
[Home Team], [Home Score]
FROM [2009 Schedule/Scores]
WHERE Nz([Away Score],0) > Nz([Home Score],0);

You can then create totals queries based on this union query to count wins
and losses as well as sum points for and against.
--
Duane Hookom
Microsoft Access MVP


Jared said:
Sorry about the links.. Was just trying to give as much information as
possible upfront as I have been stuck on this for a week now trying different
commands etc. lol

Table: Team Table
Team Name - Text - Primary Key
Previous Name - Text
Division - Text

Table: 2009 Schedule/Scores
Game Number - Autonumber - Primary Key
Date - Date/Time - Date of game on schedule
Home Team - Text - A linked lookup to Team table/Team Name
Away Team - Text - A linked lookup to Team table/Team Name
Park - Number
Diamond - Text - A linked lookup to Park List Table/Park
Time - Text - Lookup list of pre-defined times
Home Score - Number - Score of the home team after game was completed
Away Score - Number - Score of the away team after game was completed
Completed - Yes/No - Check box showing game was completed

Query: 2009 Schedule and Score Calculation
SQL View:
SELECT [2009 Schedule/Scores].[Game Number], [2009 Schedule/Scores].[Home
Team], [2009 Schedule/Scores].[Away Team], [2009 Schedule/Scores].[Home
Score], [2009 Schedule/Scores].[Away Score], IIf([Completed]=True And [Home
Score]>[Away Score],[Home Team],IIf([Completed]=True,[Away Team])) AS
WinnerTest, IIf([Completed]=True And [Home Score]<[Away Score],[Home
Team],IIf([Completed]=True,[Away Team])) AS LoserTest, IIf([Home
Team]=[WinnerTest] And [Completed]=True,+2,IIf([Completed]=True,-1,0)) AS
Hpoints, IIf([Away Team]=[WinnerTest] And
[Completed]=True,+2,IIf([Completed]=True,-1,0)) AS aPoints, [2009
Schedule/Scores].Completed, [2009 Schedule/Scores].Date, [2009
Schedule/Scores].Park, [2009 Schedule/Scores].Diamond, [2009
Schedule/Scores].Time
FROM [2009 Schedule/Scores];


I am trying to run either a report or Query to count the number of times a
teams name shows up in WinnerTest and LoserTest which will give me an output
either on the report or Query like:

Team Name Wins Loses
NutZ N Boltz 2 1

Hope this helps.

Thanks

Jared


Jared said:
Hey guys,

I am trying to create a baseball league database for a league I run. I have
run into a problem as I cannot find the right command to count the number of
times in the Score Query that a specific team has won.

I have 1 table that I am using for the team names and details
I have 1 table that I am using for the schedule and scores with a lookup
relationship to the team name table.
I am using a query on the schedule and scores and calculates the winning
team and losing team based on the scores.

I need to figure out how to run a query or report that will count the number
of times a specific team is listed in the Winner section and loser section so
I can create a standings list.

I think I am looking how to build the expression to count the number of
times winner = team name and output that value..

this is the formula I am work with in my report :
=Sum(Abs("[2009 Schedule and Score Calculation]![WinnerTest]"="[Team Name]"))

Please find the links to two pictures of my database to help out.

http://s82.photobucket.com/albums/j...iew&current=CompleteScheduleReportwscores.jpg

http://s82.photobucket.com/albums/j...ew&current=ScheduleScoresAddandEditScreen.jpg
Click to expand...
Click to expand...
 
Back
Top