# Thread: calculating results on a subdatasheet (2000)

1. ## calculating results on a subdatasheet (2000)

I have a tennis league database. A Fixture is a

2. ## Re: calculating results on a subdatasheet (2000)

I have moved this thread from Excel to Access.

3. ## Re: calculating results on a subdatasheet (2000)

Questions, questions:
- Is there a fixed number of games per match?
- Is the match result only valid if all game results are in?
- How are you storing the Result? As a text string ("1-0" or "0-1")?
- Is it necessary to store the match results in tblFixtures? You can calculate them on the fly in a query.

4. ## Re: calculating results on a subdatasheet (2000)

Hello Hans

The number of games per match can be different in each division.
The match result is only valid when all games are in.
I am storing the result as a single number ("1" or "0"). I followed your advice from my previous post when you mentioned one persons win is another's loss. At first, my logic was that each fixture involves two teams and each team has more than 1 fixture, therefore I would need a junction table. Mark Liquorman suggested I might be complicating things. So now, I have a Team (tblTeams) has many fixtures (tblFixtures) and in each Fixture there is an "Opponant" field (a lookup to tblTeams).

Then I've set tblFixtures with a 1 To Many link with tblGames. In tblGames I have fields for "Player1", "Player2" and "Result" Result is "1" for Player 1 win or "0" for Player 1 loss. The Form for tbl Fixtures contains a subform for tblGames which works fine. (A second calculated field is simply {=1-Result} ). However, I do not know how to link the results back to the actual Fixtures to calculate a league table.

Sorry if this seems long-winded. I hope it is explanatory.

Many Thanks

5. ## Re: calculating results on a subdatasheet (2000)

Still more questions. Sorry.

If the number of games per match can vary, how will you determine for which matches the final result can be computed?

6. ## Re: calculating results on a subdatasheet (2000)

Hans,

Practically speaking, a match result is only submitted, when all the match games are complete, so I hadn't really planned for this technicality.

( I would be happy enough if the overall team result just reflected the total of the games entered. - i.e. If i just entered 1 game the overall team result would be based on one game, but if I entered 10 games the overall result would be based on that

Perhaps, a final result could be calculated as soon as the "Result" fields are populated. E.g. the tblGames, has fields for Player1 & Player2 and a "Result" field. Once an entry is made in tblGames it should require an entry in the "Result" field in order to be valid. The Result can be "1", "0" or "0.5" (for a draw i.e not a boolean result).

Or perhaps, I could place a macro button on the Form to submit the result, once it is complete?

(Do you think my table design is making this more complcated?)

Many thanks

7. ## Re: calculating results on a subdatasheet (2000)

I have attached a simple example using a Totals query to compute the match result. I used 2 = win, 1 = draw, 0 = loss instead of 1 = win, 0.5 = draw and 0 = loss.

8. ## Re: calculating results on a subdatasheet (2000)

Thanks for this Hans.

I had played around with similar ideas but I'm still struggling through, at the moment. Because of the table design, the query only gives the results for one side either the (0,1 or 2 that represents the loss, draw or win for the 1st team shown), so I'm still struglling to create a query that gives a full league table. I presume the best way forward is to set up a similar query for the second team listed with a ( 2 - [Result] ) formula and then have some sort of Union Query to merge the two separate tables into one table. --- Starting to feel this would be much better in Excel!!

Thanks for the help

Rob

9. ## Re: calculating results on a subdatasheet (2000)

Actually, I can't seem to make a union query would work either, because "Home" Team and "Away" Teams are different fields which I also need to sum.

Hans' query calculates the points gained for the "Home" Team but a further calculation is also needed for the away team. I can duplicate the query and, in "zero sum" fashion, make it return the opposite result for the opposing side, but I am still left with the problem of making "TeamName" = the sum of the scores from "HomeTeam" and "Away Team" fields where Home Team = Team Name and Away team = TeamName.

I'd be extremely grateful if anyone could offer any more thoughts... Please !?

10. ## Re: calculating results on a subdatasheet (2000)

This might well be easier in Excel.

11. ## Re: calculating results on a subdatasheet (2000)

Thanks for the help. I am actually now getting close to a solution.

The Answer seems to lie with the "AS" keyword in the SQL Union Query. I can now switch the opposition results around to make a workable query.

So, basically I enter [Result] for a "Team" and then the expression (1-[Result]) to give an "Opposition score".
Then I can set up two queries - "Team" and "Opponant"
"Team" qry includes [Team] and [Result] and "Opponant" qry includes [Opponant] "Opposition Score". Then a Union Query :-

SELECT [Team], [Result]
FROM qryTeam;

UNION ALL SELECT [Opponant] AS Team, [Opposition Score] AS Result
FROM qryOpponant;

Hope this info might help others

Best Regards

Rob

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•