How do I combine these two queries?

[racefan91]

I am trying to combine these two queries and can't figure out how to combine the duplicates.



Query1

SELECT Tbl_Tournaments.Rank1, Tbl_Tournaments.Winner, Count(Tbl_Tournaments.Winner) AS CountOfWinner
FROM Tbl_Tournaments
GROUP BY Tbl_Tournaments.Rank1, Tbl_Tournaments.Winner, Tbl_Tournaments.Star1
HAVING (((Tbl_Tournaments.Star1)=[Tbl_Tournaments].[Winner]))
ORDER BY Tbl_Tournaments.Rank1, Count(Tbl_Tournaments.Winner) DESC;

Query 2

SELECT Tbl_Tournaments.Rank2, Tbl_Tournaments.Winner, Count(Tbl_Tournaments.Winner) AS CountOfWinner
FROM Tbl_Tournaments
GROUP BY Tbl_Tournaments.Rank2, Tbl_Tournaments.Winner, Tbl_Tournaments.Star2
HAVING (((Tbl_Tournaments.Star2)=[Tbl_Tournaments].[Winner]))
ORDER BY Tbl_Tournaments.Rank2, Count(Tbl_Tournaments.Winner) DESC;

I tried creating a union query and I am getting situations where a Player had the same rank in both queries so now I just want to add the number of wins they had in each together.

Here is my union query.

SELECT *
FROM [Test2]
UNION ALL SELECT [Test].*
FROM [Test];

[JoeM]

Maybe use a table or create a query that just has all your players.
Then, from this, do Left Joins to your two queries. You can then add up the Wins by adding the columns from each of the two queries.
You can use the NZ function to handle the Null cases when a person is in one query but not the other, i.e.

TotalWins:NZ([Query1]![CountOfWinner],0) + NZ([Query2]![CountOfWinner],0)

[Dal Jeanis]

Not a Union - you can do this with a join on the Winner field.

Select 
   Q1.Winner, 
   Q1.Rank1, 
   Q1.CountofWinner As Q1Count, 
   Q2.Rank2, 
   Q2.CountofWinner As Q2Count, 
   (Q1.CountofWinner + Q2.CountofWinner) As TotCount
FROM
   Query1 AS Q1 INNER JOIN Query2 AS Q2 ON Q1.Winner = Q2.Winner
ORDER BY 
   (Q1.CountofWinner + Q2.CountofWinner) DESC, 
   Q1.Rank1;

[JoeM]

Dal,

Won't that just give them people who appear in BOTH queries, but not anyone who appears in just one of those two queries?

[Dal Jeanis]

JoeM - Well, if you want to get TECHNICAL. Sheesh.

Select 
   NZ(Q1.Winner,Q2.Winner), 
   NZ(Q1.Rank1,99), 
   NZ(Q1.CountofWinner,0) As Q1Count, 
   NZ(Q2.Rank2,99), 
   NZ(Q2.CountofWinner,0) As Q2Count, 
   (NZ(Q1.CountofWinner,0) + NZ(Q2.CountofWinner,0)) As TotCount
FROM
   Query1 AS Q1 FULL OUTER JOIN Query2 AS Q2 ON Q1.Winner = Q2.Winner
ORDER BY 
   (NZ(Q1.CountofWinner,0) + NZ(Q2.CountofWinner,0)) DESC, 
   NZ(Q1.Rank1,99), 
   NZ(Q2.Rank2,99)

My first post assumes that all players appear in both queries. This one assumes that only some top number of players appears in each query, and that the number is less than 99. You'd change "99" to "999" or "129" if you were in a 128-player ladder.


Sheesh. One page said Access had a FULL OUTER JOIN, three pages said it didn't. That is stinking annoying. Ignore this post for now, I'll verify and fix the code.

[JoeM]

I tried creating a union query and I am getting situations where a Player had the same rank in both queries so now I just want to add the number of wins they had in each together.

I had interpretted that to mean that only some people appear in both queries, but not all.

Sheesh. One page said Access had a FULL OUTER JOIN, three pages said it didn't. That is stinking annoying. Ignore this post for now, I'll verify and fix the code.

Yeah, I don't think it does, which is why I suggested the approach of creating a complete player listing to start from (if one does not already exist), and do Left Joins from that to the other two queries.
I have used this approach a number of times myself.

It would be nice if Access had a Full Outer join...

[Dal Jeanis]

That sounds right.

Okay, assuming that there is already a player table somewhere, and assuming it's called tblPlayers and has a field Player that matches the values in Winner, this would report all the players that had a rank in either table -

Select 
   P1.Player, 
   NZ(Q1.Rank1,999), 
   NZ(Q1.CountofWinner,0) As Q1Count, 
   NZ(Q2.Rank2,999), 
   NZ(Q2.CountofWinner,0) As Q2Count, 
   (NZ(Q1.CountofWinner,0) + NZ(Q2.CountofWinner,0)) As TotCount
FROM
  ((tblPlayers AS P1 LEFT JOIN Query1 AS Q1 ON P1.Player = Q1.Winner)
                     LEFT JOIN Query2 AS Q1 ON P1.Player = Q2.Winner)
WHERE ((Q1.Rank1 Is Not Null) OR (Q2.Rank2 Is Not Null))
ORDER BY 
   (NZ(Q1.CountofWinner,0) + NZ(Q2.CountofWinner,0)) DESC, 
   NZ(Q1.Rank1,999), 
   NZ(Q2.Rank2,999)

[racefan91]

This has potential. The problem I'm seeing is that each rank for query 2 is showing up, somehow it has to show zero's or something. Situation is like player x has 6 wins as rank 1 in query 1 but 0 wins as rank 1 in query 2 so for some reason it is taking the first instance that player is in query 2 and it adds that to rank 1 count.

[racefan91]

I did end up getting my wins count to combine off both queries by simply using the Total and sum.

SELECT Tbl_Stars.Star, [Qry_Tournaments_Rank Wins Union].Rank1, Sum([Qry_Tournaments_Rank Wins Union].CountOfWinner) AS SumOfCountOfWinner
FROM Tbl_Stars LEFT JOIN [Qry_Tournaments_Rank Wins Union] ON Tbl_Stars.Star=[Qry_Tournaments_Rank Wins Union].Winner
GROUP BY Tbl_Stars.Star, [Qry_Tournaments_Rank Wins Union].Rank1
ORDER BY [Qry_Tournaments_Rank Wins Union].Rank1, Sum([Qry_Tournaments_Rank Wins Union].CountOfWinner) DESC;

Did the same thing for my losses count

SELECT Tbl_Stars.Star, [Qry_Tournaments_Rank Losses Union].Rank1, Sum([Qry_Tournaments_Rank Losses Union].CountOfLoser) AS SumOfCountOfLoser
FROM Tbl_Stars LEFT JOIN [Qry_Tournaments_Rank Losses Union] ON Tbl_Stars.Star=[Qry_Tournaments_Rank Losses Union].Loser
GROUP BY Tbl_Stars.Star, [Qry_Tournaments_Rank Losses Union].Rank1
ORDER BY [Qry_Tournaments_Rank Losses Union].Rank1;

The problem is now combing these two into one query. I used some code you gave me the other day and modified it to this

SELECT Tbl_Rank.Rank, TT.Star, TW.SumofCountOfWinner, TL.SumofCountOfLoser, [SumofCountOfWinner]/([SumofCountOfWinner]+[SumofCountOfLoser]) AS [Winning Percentage]
FROM Tbl_Rank INNER JOIN ((Tbl_Stars AS TT INNER JOIN [Qry_Tournaments_Rank Wins] AS TW ON TT.Star = TW.Star) INNER JOIN [Qry_Tournaments_Rank Losses] AS TL ON (TW.Rank1 = TL.Rank1) AND (TT.Star = TL.Star)) ON Tbl_Rank.Rank = TL.Rank1
WHERE ((([TW].[SumOfCountOfWinner]+[TL].[SumofCountOfLoser])>0))
ORDER BY Tbl_Rank.Rank, [SumofCountOfWinner]/([SumofCountOfWinner]+[SumofCountOfLoser]) DESC , TW.SumofCountOfWinner DESC , TL.SumofCountOfLoser;

The problem I am now having which is similar to the original question on here is there are players that have no wins but some loses or vise versa that aren't showing up.

[Dal Jeanis]

In English, what is tbl_Rank?
In English, what is tbl_Stars?
If there is a table that has all the players in it, what is that table?

[racefan91]

In English, what is tbl_Rank?
In English, what is tbl_Stars?
If there is a table that has all the players in it, what is that table?

Tbl_Rank has numbers 1 through 64. I use these in my tournament table as the rank of the player.
Tbl_Stars is the table with all the player names.

[JoeM]

I was thinking it might be good if we could see what the source data looks like, and what the exported output should look like.
Any possibility of posting some data samples or uploading your database?

[racefan91]

Attached are pdf print-outs of what I am currently accomplishing. Qry_Tournaments_Rank Winning Percentage is ultimately the look I am going for so then I can create a report off of it and group on the ranks. When looking at it you'll notice the following issue I'm having, Rank number 5 is where things show that they aren't working. Players 4,6, & 10 show up in Qry_Tournaments_ Rank Losses but they don't show up in the Winning Percentage query because they have 0 wins. Those are the players I am trying to get to show up.

Qry_Tournaments_Rank Wins Total.pdf
Qry_Tournaments_Rank Wins2.pdf
Qry_Tournaments_Rank Wins1.pdf
Qry_Tournaments_Rank Losses Total.pdf
Qry_Tournaments_Rank Losses2.pdf

[racefan91]

Qry_Tournaments.pdf
Qry_Tournaments_Rank Winning Percentage.pdf
Qry_Tournaments_Rank Losses1.pdf

[Dal Jeanis]

All right, if Stars is your players table, then put that at the far left of a left join on your win/loss queries. That eliminates duplicates on Player.

Rank is meaningless across two different tournaments, so you probably want the players ordered by either their Winning Rank, or the Lower (Best) of their (potentially) two Ranks.

Try this and see what it looks like:

SELECT 
   TT.Star, 
   IIF(NZ(TW.Rank1,999)<NZ(TL.Rank1,999),NZ(TW.Rank1,999),NZ(TL.Rank1,999)) As BestRank
   NZ(TW.SumofCountOfWinner,0) As WinCount, 
   NZ(TL.SumofCountOfLoser,0) As LoseCount, 
   NZ([TW.SumofCountOfWinner])/(NZ([TW.SumofCountOfWinner],0)+NZ([TW.SumofCountOfLoser],0)) AS [Winning Percentage]
FROM 
  ((Tbl_Stars AS TT 
      LEFT JOIN [Qry_Tournaments_Rank Wins] AS TW ON TT.Star = TW.Star) 
      LEFT JOIN [Qry_Tournaments_Rank Losses] AS TL ON TT.Star = TL.Star)
WHERE 
   (((NZ([TW].[SumOfCountOfWinner],0)+NZ([TL].[SumofCountOfLoser],0))>0))
ORDER BY 
   NZ([TW.SumofCountOfWinner])/(NZ([TW.SumofCountOfWinner],0)+NZ([TW.SumofCountOfLoser],0)), 
   NZ(TW.SumofCountOfWinner,0) DESC, 
   NZ(TL.SumofCountOfLoser,0);