How do I combine these two queries?

[racefan91]

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);

I'm getting an error that says invalid bracketing of name '[TW.SumofCountOfLoser]'

[racefan91]

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.

Rank isn't necessarily meaningless across different tournaments. Example would be like this, Player1 has Rank1 in tournaments 1, 2, & 3. So I want to combine their wins/losses together from each of those three tournaments.

[Dal Jeanis]

1) My typo. Brackets, if present, in each case should be around the table and the individual field - [TW].[SumofCountOfLoser]

2) Maybe we have different definitions of meaningless.

If you have a query that includes a single tournament, then the statistics organized by rank, and showing a player's win/loss to get there, has a clear meaning.

On the other hand, if you have three tournaments, and different players getting the same ranks with different win/loss, and the same player getting the same or different ranks, then I have no idea what ordering by the ranks each player got in various tournaments, and combining the win/losses, actually means.

To make a meaningful query of that, I suggested that you take the player's lowest/best rank as his place on the list, and then combine his win/loss figures. That says, this guy who achieved Rank 1 in at least one tournament, overall won X, lost Y, and had a win ratio (X/(X+Y)). I can see how that information is useful.

Other than that, I can't see what the meaning of your desired query is, related to the data.

[racefan91]

1) My typo. Brackets, if present, in each case should be around the table and the individual field - [TW].[SumofCountOfLoser]

2) Maybe we have different definitions of meaningless.

If you have a query that includes a single tournament, then the statistics organized by rank, and showing a player's win/loss to get there, has a clear meaning.

On the other hand, if you have three tournaments, and different players getting the same ranks with different win/loss, and the same player getting the same or different ranks, then I have no idea what ordering by the ranks each player got in various tournaments, and combining the win/losses, actually means.

To make a meaningful query of that, I suggested that you take the player's lowest/best rank as his place on the list, and then combine his win/loss figures. That says, this guy who achieved Rank 1 in at least one tournament, overall won X, lost Y, and had a win ratio (X/(X+Y)). I can see how that information is useful.

Other than that, I can't see what the meaning of your desired query is, related to the data.

Ahh, I see where your thinking was. My thought was a little different. A player enters a tournament with a specific rank that doesn't change as the tournament progresses. It can only change after a tournament is finished. Take Player 1 as an example, they entered Tournament 1 as the overall the number 1 ranked team and had 3 wins and 1 loss. Then in tournament 2 they still started out as the overall number 1 ranked team and had 2 wins and 1 loss. Tournament 3 is the last time they have been the number 1 ranked team and they had 0 wins and 1 loss. So what I'm trying to do is get this query to say that when Player 1 was the number 1 ranked team they had a combine record of 5 wins and 3 losses. Then it would display whoever else has had the number 1 ranking and what their record is so far; all the way through the 64 different rankings. I can get this to run through a form with criteria but that will only display the 1 rank I manually enter in as the criteria. Hope this helps clear up what exactly I am trying to accomplish. Thanks