Bill
Try making this change in your crosstab:
PIVOT t_EventParticipants.gteResID IN(1,2);
Specifying the values you expect should force them to show and not give you the <> field name.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> 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