Bill,
The first query is the Crosstab query, the second is a query that uses the
Crosstab query
TRANSFORM Count(t_EventParticipants.gteGteID) AS CountOfgteGteID
SELECT t_EventParticipants.gteTnaID, t_TeamName.tnaName AS TeamName,
t_Grade.graName, Count(t_EventParticipants.gteGteID) AS TotalGames
FROM (t_Grade INNER JOIN t_TeamName ON t_Grade.graGraID=t_TeamName.tnaGraID)
INNER JOIN (t_FacilitySchedule INNER JOIN t_EventParticipants ON
t_FacilitySchedule.fscFscID=t_EventParticipants.gteFscID) ON
t_TeamName.tnaTnaID=t_EventParticipants.gteTnaID
WHERE (((t_FacilitySchedule.fscEveID)=1) AND ((t_TeamName.tnaSpoID)=1))
GROUP BY t_EventParticipants.gteTnaID, t_TeamName.tnaName, t_Grade.graName,
t_FacilitySchedule.fscEveID, t_TeamName.tnaSpoID
PIVOT t_EventParticipants.gteResID;
2nd query. Here is where I was trying to change Null to Zero.
SELECT q_WinLossCrosstab.gteTnaID, q_WinLossCrosstab.TeamName,
q_WinLossCrosstab.graName, q_WinLossCrosstab.TotalGames,
q_WinLossCrosstab.[<>] AS NotScored, q_WinLossCrosstab.[1],
q_WinLossCrosstab.[2]
FROM q_WinLossCrosstab
WHERE (((q_WinLossCrosstab.[1])=IIf([1] Is Null,0,[1])))
ORDER BY q_WinLossCrosstab.graName;
Bill
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Monday, March 05, 2012 12:56 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Null Problems
Bill
Can you post the SQL statement here so we have something to work with?
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile/Bill.Mosca
--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Singer"
<Bill.Singer@...> wrote:
>
> I am having problems with a Null value.
>
> I ran a crosstab query to calculate wins and losses of a team. Wins are
> designated by a [1] in the field and losses by a [2]. The crosstab query
> worked great, but I also want to calculate winning percentage like you see
> when you look at baseball standings. The problems is that if a team has
won
> or lost all of their games the crosstab query returns an empty field. In
> the next query that pulls values from my crosstab query I would like to
> replace that Null values with a "0" so I can use the number for
> calculations.
>
>
>
> I tried this.
>
>
>
> IIf([1] is Null,0,[1]) ..in the Criteria row of the query.
>
>
>
> 1 is the value that indicates a win, and is also the name of the field in
> the crosstab query.
>
>
>
> I start out with 17 records in my query. 3 of them have and emply [1]
> field, which is the win column. When I insert the above IIf statement in
> the CRITERIA row of the query, my query runs but is only shows 14 records.
> The 3 records with the Null values in the win field do not show. I would
> like all 17 records with "0" put in the Null [1] field for those teams
that
> have zero wins. How do I make that change?
>
>
>
> IIf([2] is Null,0,[2]) is what I also want to do if a team has all wins
> and no losses. The [2] field of the crosstab record records losses.
>
>
>
> I am doing something wrong.
>
> Thank you for your help.
>
>
>
> Bill
>
> MN
>
>
>
> [Non-text portions of this message have been removed]
>
_____
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2114/4852 - Release Date: 03/05/12
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar