Cross tab query

[MN]

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");

 

[KARL DEWEY]

Change to --
PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");

 

[MN]

Thank you

Change to --
PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");

--
Build a little, test a little.

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");

 

[MN]

Uh... I got an error :"Can't specify fixed column heading 'Q4' in a crosstab
query more than once." Thanks .

Change to --
PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");

--
Build a little, test a little.

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");

 

[KARL DEWEY]

Did you?

Post back your complete SQL.

--
Build a little, test a little.

Uh... I got an error :"Can't specify fixed column heading 'Q4' in a crosstab
query more than once." Thanks .

Change to --
PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");

--
Build a little, test a little.

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");

 

[MN]

Huh ... So sorry ... It working now. I do not know why I got an err before?
I delete the query and working from the begin then it work!?

Sorry for any confusion-Regards,
MN

Did you?

Post back your complete SQL.

--
Build a little, test a little.

Uh... I got an error :"Can't specify fixed column heading 'Q4' in a crosstab
query more than once." Thanks .

Change to --
PIVOT "Q" & Format([cln_cls_dt],"q") IN("Q1", "Q2", "Q3", "Q4");

--
Build a little, test a little.


:

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");