Prev: Find Replace values when they are not null
Next: Convert seconds to hours
From: MN on 6 Apr 2010 17:12
Hi - I have this query below, but the data do not have First quarter, so how
can I list them on the report? Thank you for any reply.

Query come out with the result like this:
County Countyname Q2 Q3 Q4

TRANSFORM Count([client].[county]) AS CountOfCountycode
SELECT [client].[county], [CountyCode].[Countyname]
FROM client INNER JOIN CountyCode ON [client].[county]=[CountyCode].[Cntycode]
GROUP BY [client].[county], [CountyCode].[Countyname]
PIVOT "Q" & Format([cln_cls_dt],"q");

From: KARL DEWEY on 6 Apr 2010 18:08
Change to --
PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");

--
Build a little, test a little.


"MN" wrote:

> Hi - I have this query below, but the data do not have First quarter, so how
> can I list them on the report? Thank you for any reply.
>
> Query come out with the result like this:
> County Countyname Q2 Q3 Q4
>
> TRANSFORM Count([client].[county]) AS CountOfCountycode
> SELECT [client].[county], [CountyCode].[Countyname]
> FROM client INNER JOIN CountyCode ON [client].[county]=[CountyCode].[Cntycode]
> GROUP BY [client].[county], [CountyCode].[Countyname]
> PIVOT "Q" & Format([cln_cls_dt],"q");
>
From: MN on 7 Apr 2010 08:45

Thank you
"KARL DEWEY" wrote:

> Change to --
> PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");
>
> --
> Build a little, test a little.
>
>
> "MN" wrote:
>
> > Hi - I have this query below, but the data do not have First quarter, so how
> > can I list them on the report? Thank you for any reply.
> >
> > Query come out with the result like this:
> > County Countyname Q2 Q3 Q4
> >
> > TRANSFORM Count([client].[county]) AS CountOfCountycode
> > SELECT [client].[county], [CountyCode].[Countyname]
> > FROM client INNER JOIN CountyCode ON [client].[county]=[CountyCode].[Cntycode]
> > GROUP BY [client].[county], [CountyCode].[Countyname]
> > PIVOT "Q" & Format([cln_cls_dt],"q");
> >
From: MN on 7 Apr 2010 09:04
Uh... I got an error :"Can't specify fixed column heading 'Q4' in a crosstab
query more than once." Thanks .

"KARL DEWEY" wrote:

> Change to --
> PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");
>
> --
> Build a little, test a little.
>
>
> "MN" wrote:
>
> > Hi - I have this query below, but the data do not have First quarter, so how
> > can I list them on the report? Thank you for any reply.
> >
> > Query come out with the result like this:
> > County Countyname Q2 Q3 Q4
> >
> > TRANSFORM Count([client].[county]) AS CountOfCountycode
> > SELECT [client].[county], [CountyCode].[Countyname]
> > FROM client INNER JOIN CountyCode ON [client].[county]=[CountyCode].[Cntycode]
> > GROUP BY [client].[county], [CountyCode].[Countyname]
> > PIVOT "Q" & Format([cln_cls_dt],"q");
> >
From: KARL DEWEY on 7 Apr 2010 10:39
Did you?

Post back your complete SQL.

--
Build a little, test a little.


"MN" wrote:

> Uh... I got an error :"Can't specify fixed column heading 'Q4' in a crosstab
> query more than once." Thanks .
>
> "KARL DEWEY" wrote:
>
> > Change to --
> > PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");
> >
> > --
> > Build a little, test a little.
> >
> >
> > "MN" wrote:
> >
> > > Hi - I have this query below, but the data do not have First quarter, so how
> > > can I list them on the report? Thank you for any reply.
> > >
> > > Query come out with the result like this:
> > > County Countyname Q2 Q3 Q4
> > >
> > > TRANSFORM Count([client].[county]) AS CountOfCountycode
> > > SELECT [client].[county], [CountyCode].[Countyname]
> > > FROM client INNER JOIN CountyCode ON [client].[county]=[CountyCode].[Cntycode]
> > > GROUP BY [client].[county], [CountyCode].[Countyname]
> > > PIVOT "Q" & Format([cln_cls_dt],"q");
> > >
 |  Next  |  Last
Pages: 1 2
Prev: Find Replace values when they are not null
Next: Convert seconds to hours