Help With Query where count is 0

[racefan91]

I have a tournament table where I have columns Region 1, Star1, Region 2, Star 2, Winner, and Loser. I am trying to run a query on my tournament table where my Star1 column is the same as my Winner column. I also want to show in my query Region 1 and do a group on that and a count on the winner. Basically to show how many times a star has won from a region. Is there a way to get it to show all 4 regions and if they don't have a win the put in a zero?

If that's not possible then how would I go about this situation. I have a query that produces the winner count for region 1 and the winner count for region 2. In some cases the star wins in two different region 1's (ex. North and South) and 3 different region 2's (ex. North, South, and East). I am trying to then combine the two queries together and add the regions for example if they had 2 wins in the North region from query 1 and 1 win from the North region in query 2 it would then show 3 wins in my Totals query. The problem I am having is the regions that show up in only one of the first two queries don't show up in the Totals query, I think because one is null. Is there any way to get that region to show up with just the count from the query it shows a result?

Here is the code for my Totals Count Query

SELECT Tbl_Stars.Star,
Tbl_Regions.Region,
([~Qry_Tournaments_Region1 Wins Count].CountOfWinner+[~Qry_Tournaments_Region2 Wins Count].CountOfWinner) AS Wins
FROM Tbl_Regions
INNER JOIN ((Tbl_Stars INNER JOIN [~Qry_Tournaments_Region2 Wins Count] ON Tbl_Stars.Star = [~Qry_Tournaments_Region2 Wins Count].Winner)
INNER JOIN [~Qry_Tournaments_Region1 Wins Count] ON Tbl_Stars.Star = [~Qry_Tournaments_Region1 Wins Count].Winner)
ON (Tbl_Regions.Region = [~Qry_Tournaments_Region1 Wins Count].Region1) AND (Tbl_Regions.Region = [~Qry_Tournaments_Region2 Wins Count].Region2)
ORDER BY Tbl_Stars.Star, Tbl_Regions.Region;

[ssanfu]

Maybe use the Nz() function in the sub queries "~Qry_Tournaments_Region1 Wins Count" & "~Qry_Tournaments_Region2 Wins Count".

(BTW, you shouldn't use special characters or spaces in object names)

The main problem, IMO, is you do not have a normalized structure.
I would suggest "Tbl_Regions" would be


Tbl_Regions
RegionID_PK (autonumber)
RegionNum (Integer) (1, 2, 3, ..., 10099)
Quadrant (text) (North, South, East,West) (or could be a lookup table)
Star_FK (Long Integer) (link to Tbl_Stars)
Winner (??)
Loser (??)

------------------------
Maybe see http://b62.tripod.com/doc/dbbase.htm

[racefan91]

The nz doesn't seem to work. I am getting the same results with or without it.

There is more to my Tbl_Tournaments than the couple of columns I described. Those are just the ones I am trying to use to get my results for the query. The reason I am using two different regions is for situations like the final four or championship where the two contenders would have different regions.