Win/Loss Statistics

[Annette]

I have a database that keeps track of a participants wins and losses
in regards to wrestling. I would like to have a query that lists the
participants, the tournament they wrestled in and their record (win/
total matches). This is quite straight forward, but I would also like
to have a row total for their overall win/total matches and a column
total for the teams overall win/total matches for a meet.

So it would look like this:

Participant SanFran Dallas
Detroit Overall
Jones, A 2/5 3/3
2/2 7/10
Miller, B 5/5 3/3
2/2 10/10
Paulson, S 0/5 0/3
0/2 0/10
Summary 7/15 6/9
4/6 17/30

I currently have a crosstab query that can give me the participants
and the records for each city, but not overall.

Plus I don't know how to get the summary total by column.

Thanks for helping!!!

 

[John Spencer]

First of all, post the SQL of the crosstab you do have. Hint: Menu:
View: SQL.

Someone may be able to modify the query to give you what you want.


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

 

[Ken Sheridan]

If you are starting off with a query along these lines:

TRANSFORM NZ(COUNT(Win),0) & "/" & NZ(COUNT(*),0)
SELECT Competitor
FROM Tournaments
GROUP BY Competitor
PIVOT Tournament;

You can add the Overall column like so:

TRANSFORM NZ(COUNT(Win),0) & "/" & NZ(COUNT(*),0)
SELECT Competitor,
NZ(COUNT(Win),0) & "/" & NZ(COUNT(*),0) AS Overall
FROM Tournaments
GROUP BY Competitor
PIVOT Tournament;

Save this as Q1.

You can then produce a summary row with:

TRANSFORM NZ(COUNT(Win),0) & "/" & NZ(COUNT(*),0)
SELECT "Summary" As Summary,
NZ(COUNT(Win),0) & "/" & NZ(COUNT(*),0) AS Overall
FROM Tournaments
GROUP BY "Summary"
PIVOT Tournament;

Save this as Q2.

You can then produce the final result by means of a UNION ALL operation:

SELECT *
FROM Q1
UNION ALL
SELECT *
FROM Q2;

The above assumes that you have a row for each bout with a column Win which
will have an value in the case of a win, but be Null in the case of a loss.
If you have a Boolean (Yes/No) column for a win or loss (Yes for a win) then
you'd use a different expression to count the wins/totals such as:

NZ(SUM(IIF Win,1,0),0) & "/" & NZ(COUNT(*),0)

If on the other hand you are simply storing the wins/totals per competitor
per tournament as a text value such as '2/5' rather than recording each bout
individually You can't do it so easily. I think you'd have to write VBA
functions which parse the string to get the 'win' and 'total' values per
competitor per tournament and use the return values of these functions to
compute the 'summary' and 'overall' values. Fundamentally, the reason you'd
have to resort to this would be that more than one item of data is being
stored in each column in each row, which means the table is not even
normalized to First Normal Form. But lets hope that's not the case.

Ken Sheridan
Stafford, England