Bill-
Try this:
PARAMETERS [Forms]![f_RecordRankingChooser]![cboSport] Long;
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)=[Forms]![f_RecordRankingChooser]![cboSport]))
GROUP BY t_EventParticipants.gteTnaID, t_TeamName.tnaName, t_Grade.graName,
t_FacilitySchedule.fscEveID, t_TeamName.tnaSpoID
PIVOT t_EventParticipants.gteResID IN ("[<>]", "[1]", "[2]");
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@yahoogroups.com] On Behalf Of agent1of6
Sent: Wednesday, March 07, 2012 8:51 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Crosstab problems
Duane,
I did a bit a reading on setting column Headings property but I need to learn
more. Not enought time before work.
Here is the query that is the source of the report.
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, Nz([q_WinLossCrosstab].[3],0) AS Three,
[One]/([TotalGames]-[NotScored]) AS Percentage,
IIf([One]>0,([One]/([TotalGames]-[NotScored])),0) AS [Percent],
t_TeamName.tnaSpoID
FROM q_WinLossCrosstab INNER JOIN t_TeamName ON q_WinLossCrosstab.gteTnaID =
t_TeamName.tnaTnaID
WHERE (((t_TeamName.tnaSpoID)=[Forms]![f_RecordRankingChooser]![cboSport]))
ORDER BY q_WinLossCrosstab.graName;
Here is the crosstab query.
PARAMETERS [Forms]![f_RecordRankingChooser]![cboSport] Long;
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))
GROUP BY t_EventParticipants.gteTnaID, t_TeamName.tnaName, t_Grade.graName,
t_FacilitySchedule.fscEveID, t_TeamName.tnaSpoID
PIVOT t_EventParticipants.gteResID;
I will do some more reading on column headings today.
Thanks so much.
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@...> wrote:
>
> What is the SQL of your query? If you are using this as the record source for
a report, are you setting the Column Headings property or how are you ensuring
all fields get generated?
>
> Duane
>
> --- In MS_Access_Professionals@yahoogroups.com, "agent1of6" <Bill.Singer@>
wrote:
> >
> > Bill,
> > Well that worked. WOW. I would not have figured that our. I set the data
type of [Forms]![f_RecordRankingChooser]![cboSport] as an Integer. I also tried
a text just for fun and as expected it did not work. The 2nd query which is
the report query pulls from the crosstab query. Both queries run correctly and
sort per the combo box on my form.
> >
> > But now the report does not run because it 2nd report query shows no
available fields.
> >
> > When I run the report it says "The Microsoft Access database engine does not
recogonize " as a valid field name of expression"
> >
> > I tried to design a new report off of the 2nd query and in the report wizard
the query shows no fields available.
> >
> > Another error message that is similar but I am not sure what next.
> >
> > ....except for tonight, sleep is next.
> >
> > Thanks for your help.
> > Bill
> > MN
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > Bill-
> > >
> > > Open your Crosstab query in Design view. Open the Parameters dialog.
Define
> > > [Forms]![f_RecordRankingChooser]![cboSport] and set its data type from the
> > > drop-down list. Save your query.
> > >
> > > John Viescas, author
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar