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