You could try force values into the crosstab with:
TRANSFORM Val(Nz(Count(t_EventParticipants.gteGteID),0)) 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 IN (1,2);
Duane Hookom
MS Access MVP
--- In MS_Access_Professionals@yahoogroups.com, "Bill Singer" <Bill.Singer@...> wrote:
>
> Bill,
>
> It did just as you said. No <> field name, which is nice.
>
> I still need to know how to replace the blank fields with a Zero.
>
> I have attached a table of the crosstab query results. I do not know if it
> will come through.
>
> Record 1, 7,8, and 9 have blank fields.
>
>
>
>
>
>
> q_WinLossCrosstab
>
>
> gteTnaID
>
> TeamName
>
> graName
>
> TotalGames
>
> 1
>
> 2
>
>
> 1
>
> Raptors
>
> 9 th
>
> 8
>
> 8
>
>
>
> 3
>
> Timberwolves
>
> 9 th
>
> 7
>
> 2
>
> 2
>
>
> 4
>
> Wizards
>
> 9 th
>
> 7
>
> 1
>
> 4
>
>
> 5
>
> Heat
>
> 9 th
>
> 7
>
> 1
>
> 5
>
>
> 7
>
> Celtics
>
> 9 th
>
> 7
>
> 1
>
> 2
>
>
> 13
>
> Jazz
>
> 9 th
>
> 7
>
> 3
>
> 2
>
>
> 14
>
> Pistons
>
> 9 th
>
> 1
>
> 1
>
>
> 16
>
> Anoka
>
> 8 th
>
> 1
>
>
>
> 28
>
> Clippers
>
> 8 th
>
> 1
>
>
>
> 42
>
> Rockets
>
> 7 th
>
> 6
>
> 2
>
>
>
> 43
>
> Celtics
>
> 7 th
>
> 7
>
> 2
>
> 1
>
>
> 44
>
> Spurs
>
> 7 th
>
> 5
>
> 1
>
> 3
>
>
> 45
>
> Warriors
>
> 7 th
>
> 7
>
> 2
>
> 3
>
>
> 46
>
> Nets
>
> 7 th
>
> 7
>
> 2
>
> 2
>
>
> 47
>
> Clippers
>
> 7 th
>
> 5
>
> 2
>
> 1
>
>
> 60
>
> Thunder
>
> 7 th
>
> 8
>
> 2
>
> 2
>
>
> 61
>
> Knicks
>
> 7 th
>
> 5
>
> 1
>
> 2
>
>
>
> Bill Singer
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> Sent: Monday, March 05, 2012 1:34 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Null Problems
>
>
>
>
>
> 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
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Bill Mosca
> > Sent: Monday, March 05, 2012 12:56 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> > <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]
> >
>
>
>
> _____
>
> 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