Help with Combining Queries

[racefan91]

I have two queries, one that counts the times a team has beaten a seed in a tournament and one that counts the times a team has lost to a seed in tournament. One of the queries returns 18 results and one returns 21. What I am trying to do is combine the two queries so I can calculate a winning percentage to see who has been doing the best. How do i get the teams that aren't in both queries to show up with zeros in either the wins or losses column? Currently I am only getting the results of teams that have both a win and a loss.

Here is my sql:
SELECT Tbl_Teams.Team, [Copy Of Qry_Tournaments_Sort vs Winner Count].CountOfWinner, [Copy Of Qry_Tournaments_Sort vs Loser Count].CountOfLoser


FROM (Tbl_Teams INNER JOIN [Copy Of Qry_Tournaments_Sort vs Winner Count] ON Tbl_Teams.Team = [Copy Of Qry_Tournaments_Sort vs Winner Count].Team) INNER JOIN [Copy Of Qry_Tournaments_Sort vs Loser Count] ON Tbl_Teams.Team = [Copy Of Qry_Tournaments_Sort vs Loser Count].Team;

[JoeM]

Do a LEFT OUTER join from this table to each of your other queries. This will make sure that no records from your main table are dropped in the event that there isn't a match.

You can then use the NZ function to convert the Nulls from non-matched records to zeroes, i.e.
NZ( [Copy Of Qry_Tournaments_Sort vs Winner Count].CountOfWinner,0)+0
will return the Count, or a zero in the case of no match.

[racefan91]

When I do the LEFT OUTER I end up with all 64 teams in my teams list, most of which have 0 wins and 0 losses. Is there a way to code in to not show a team if they have a zero in both columns?

[Dal Jeanis]

to answer the question, you need to post the SQL for [Copy Of Qry_Tournaments_Sort vs Winner Count] and [Copy Of Qry_Tournaments_Sort vs Loser Count].

Ideally, you just want to make sure that those queries return a zero record for the teams that never won/lost against a seed, and then do an inner join.

[JoeM]

When I do the LEFT OUTER I end up with all 64 teams in my teams list, most of which have 0 wins and 0 losses. Is there a way to code in to not show a team if they have a zero in both columns?

That is what the Criteria row in the Query Builder is for!
Give it a shot, and if you can't figure it out, post back.

[racefan91]

Thanks, that worked. Changed those individual queries to Left Join and then this query back to Inner Join and now I'm getting the 16 results like I am looking for.

[racefan91]

Upon closer inspection I am getting all the teams from the loser query but it's still leaving out the ones from the winner query with 0 losses.

[racefan91]

Here is my sql

Winner Query:
SELECT Tbl_Teams.Team, Count([Qry_Tournaments_Sort Loser].Winner) AS CountOfWinner
FROM Tbl_Teams LEFT JOIN [Qry_Tournaments_Sort Loser] ON Tbl_Teams.Team = [Qry_Tournaments_Sort Loser].Winner
GROUP BY Tbl_Teams.Team
ORDER BY Count([Qry_Tournaments_Sort Loser].Winner) DESC;

Loser Query:
SELECT Tbl_Teams.Team, Count([Qry_Tournaments_Sort Winner].Loser) AS CountOfLoser
FROM Tbl_Teams LEFT JOIN [Qry_Tournaments_Sort Winner] ON Tbl_Teams.Team = [Qry_Tournaments_Sort Winner].Loser
GROUP BY Tbl_Teams.Team
ORDER BY Count([Qry_Tournaments_Sort Winner].Loser) DESC;

Combined:
SELECT Tbl_Teams.Team, [Copy Of Qry_Tournaments_Sort vs Winner Count].CountOfWinner, [Copy Of Qry_Tournaments_Sort vs Loser Count].CountOfLoser
FROM (Tbl_Teams INNER JOIN [Copy Of Qry_Tournaments_Sort vs Winner Count] ON Tbl_Teams.Team = [Copy Of Qry_Tournaments_Sort vs Winner Count].Team) INNER JOIN [Copy Of Qry_Tournaments_Sort vs Loser Count] ON Tbl_Teams.Team = [Copy Of Qry_Tournaments_Sort vs Loser Count].Team;

[JoeM]

Do you just have the one table?

It be helpful to see its structure and same data.

[Dal Jeanis]

The left join will get you the zeroes, so all you need to do is eliminate the teams with no wins and no losses.

Combined:
SELECT 
  TT.Team, 
  TW.CountOfWinner, 
  TL.CountOfLoser
FROM 
  (Tbl_Teams AS TT 
   INNER JOIN 
  [Copy Of Qry_Tournaments_Sort vs Winner Count] AS TW 
  ON TT.Team = TW.Team) 
  INNER JOIN 
  [Copy Of Qry_Tournaments_Sort vs Loser Count] AS TL 
  ON TT.Team = TL.Team
WHERE ((TW.CountOfWinner + TL.CountOfLoser) > 0);

[racefan91]

No, the main table I doing my queries off is my tournament table where I have the tournament, round, region, seed, etc.

I can post that if there is no way to do the following. I changed the inner join to left outer which displayed all the teams. I then did the nz(count... to turn the blanks into zeros. Is there now a way to just eliminate the teams that have a zero in both the winner and loser column by entering something in the criteria?

[JoeM]

Is there now a way to just eliminate the teams that have a zero in both the winner and loser column by entering something in the criteria?

Just place

<>0

under the Criteria row under the one calculated column, and then place the same thing under the other on the second criteria row (do NOT place both on the same row).

Basically, that is saying only return records if one of those two fields is not zero (so if they are both zero, they will be excluded).

[racefan91]

The left join will get you the zeroes, so all you need to do is eliminate the teams with no wins and no losses.

Combined:
SELECT 
  TT.Team, 
  TW.CountOfWinner, 
  TL.CountOfLoser
FROM 
  (Tbl_Teams AS TT 
   INNER JOIN 
  [Copy Of Qry_Tournaments_Sort vs Winner Count] AS TW 
  ON TT.Team = TW.Team) 
  INNER JOIN 
  [Copy Of Qry_Tournaments_Sort vs Loser Count] AS TL 
  ON TT.Team = TL.Team
WHERE ((TW.CountOfWinner + TL.CountOfLoser) > 0);

Thanks for the code. This seems to be doing exactly what I wanted it to now.

[Dal Jeanis]

Great! Please mark thread solved. Top of page, under "thread tools".