COUNTIF

[Tdahlman]

For an excuse to play with Access 2007, I have created a database with all of
my Fantasy Football league information. I have 2 Tables that are as follows:
Scores: First Table
Score ID - AutoNumber
Team ID - Number (from my 2nd table)
Week
Projected Points
Actual Points
Outcome

Team: 2nd Table
Team ID - AutoNumber
Team Name
Owner Name
Email
Phone

I want to create a query that will count how many wins each team has.
[Scores].[Outcome] has 2 possible options (Win and Lose). So how can I do a
countIf that will count how many Wins each team name has associated with it?

Thank you in advance for your help.
Travis

 

[John Spencer]

The SQL statement would look like:

SELECT [Team Name], Count(OutCome) as Wins
FROM Team INNER JOIN Scores
ON Team.[Team ID] = Scores.[Team ID]
WHERE OutCome = "Win"
GROUP BY [Team Name]

To build that query in design view you would need to
== OPen a new query
== Add the Scores and Team tables
== Join the tables by dragging from Team ID to Team ID
== Add the Team Name and Outcome fields to the list of fields
== Add the Outcome field a second time
== Select View: Totals from the menu errr Ribbon
== Change Group by to To Count under the first Outcome field
== Change Group by to WHERE under the second Outcome field
== Set criteria under the second Outcome field to
= "Win"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County