Query with INNER Join

  • Thread starter Thread starter NeoFax
  • Start date Start date
N

NeoFax

I am having problems getting the below SQL to work:

SELECT tblResults.Team, Count(tblResults.Loser) AS WinsAgainst,
tblResults.Team1
FROM tblResults INNER JOIN
(
SELECT Count(tblResults.Winner) AS CountOfWinner
FROM tblResults
WHERE (((tblResults.Team)="Dom's A")) OR (((tblResults.Team1)="Dom's
A"))
) AS XX
GROUP BY tblResults.Team, tblResults.Team1
HAVING (((tblResults.Team)="Dom's A"))
ON tblResults.Team = XX.Team;

I want to be able to ad up how many times a team played another team
and then use this result to calculate the percentage of wins against
the other team. Right now I am hard coding the team in, but once this
works I will use a form to allow it to be ran dynamically.
 
Can you explain a little more about your table structure and maybe give some
examples of the data?  This may not be the most efficient way to do this.

My initial analysis indicates that:
1.  You failed to include the Team field in the subquery, yet you triedto
join on that field
2.  The ON portion of the JOIN needs to be before the GroupBy and Having
clauses
3.  I'm not really sure the GroupBy and Having clauses are correct.

Dale








--
HTH

Dale Fye

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -
Click to expand...

Okay. The table tblResults is setup like so:

Date | Winner | Team | Points | Loser | Team1

Probably not the greatest way to do it, but it is not my database, I
am trying to fix it for a co-worker. The table contains the date the
game was played with each player from both teams in either the winner
or loser field. So, I would like to count each time team X played
team Y and then count how many times team X won in that original
count. I can do this now with three queries, but would like to do it
in one.
 
Back
Top