Counting the number of Matching items in a table

[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..

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

 

[Jared]

I forgot to add the formula I have been trying to get work, but not sure I am
barking up the right tree.

=Sum(Abs("[2009 Schedule and Score Calculation]![WinnerTest]"="[Team Name]"))

Jared

 

[John W. Vinson]

I forgot to add the formula I have been trying to get work, but not sure I am
barking up the right tree.

=Sum(Abs("[2009 Schedule and Score Calculation]![WinnerTest]"="[Team Name]"))

This will count the number of times that the text strings "[2009 Schedule and
Score Calculation]![WinnerTest]" and "[Team Name]" are identical. I can say
with great confidance that they never will be!

Lose the quotemarks, for one thing. Secondly, posting links to websites with
pictures of datasheets is not the best way to communicate! Instead, please
post a *TEXT* description of your table, perhaps in the form

Tablename
Fieldname (datatype) (meaning)
Fieldname (datatype) (meaning if not obvious)

Identify the primary key of each table. To post a query open it in SQL view
and post the SQL text here - it may look cryptic but lots of us can read it
easily.

 

[Jared]

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

I forgot to add the formula I have been trying to get work, but not sure I am
barking up the right tree.

=Sum(Abs("[2009 Schedule and Score Calculation]![WinnerTest]"="[Team Name]"))

This will count the number of times that the text strings "[2009 Schedule and
Score Calculation]![WinnerTest]" and "[Team Name]" are identical. I can say
with great confidance that they never will be!

Lose the quotemarks, for one thing. Secondly, posting links to websites with
pictures of datasheets is not the best way to communicate! Instead, please
post a *TEXT* description of your table, perhaps in the form

Tablename
Fieldname (datatype) (meaning)
Fieldname (datatype) (meaning if not obvious)

Identify the primary key of each table. To post a query open it in SQL view
and post the SQL text here - it may look cryptic but lots of us can read it
easily.