John and Duane,
I will try each of your suggestions at home tonight.
Thanks so much.
Bill Singer
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Monday, March 05, 2012 1:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Null Problems
Bill & Bill-
PMJI, but it looks like Bill Singer wants the <> column. What he wants is
the
Null columns to appear as 0, but his WHERE clause is simply filtering out
those
rows. I would try this in the second query:
SELECT q_WinLossCrosstab.gteTnaID, q_WinLossCrosstab.TeamName,
q_WinLossCrosstab.graName, q_WinLossCrosstab.TotalGames,
NZ(q_WinLossCrosstab.[<>], 0) AS NotScored, NZ(q_WinLossCrosstab.[1], 0)
AS One,
NZ(q_WinLossCrosstab.[2], 0) As Two
FROM q_WinLossCrosstab
ORDER BY q_WinLossCrosstab.graName;
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Nashua, NH)
-----Original Message-----
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 2:34 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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]
>
------------------------------------
Yahoo! Groups Links
_____
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