column headings in CrossTab Qry?

[Guest]

Hi
I have a CrossTab Qry with 2 Rows and Columns with a dates:
"Oct-06","Nov-06","Dec-06","Jan-07","Feb-07"
I have put the above dates in the Properties/ColumnHeadings dialogue, but
get no headings...?
SQL: TRANSFORM Sum(Species_caught.Num_kept) AS SumOfNum_kept
SELECT Areas.Area_details, Species_names.species_common_name
FROM Areas INNER JOIN ([Trip Record] INNER JOIN (Species_names INNER JOIN
Species_caught ON Species_names.Species_code = Species_caught.Species_code)
ON [Trip Record].Trip_ID = Species_caught.Trip_ID) ON Areas.Area_Code = [Trip
Record].Area_code
WHERE (((Format([Date],"mmm-yy"))="Oct-06" Or
(Format([Date],"mmm-yy"))="Nov-06" Or (Format([Date],"mmm-yy"))="Dec-06" Or
(Format([Date],"mmm-yy"))="Jan-07" Or (Format([Date],"mmm-yy"))="Feb-07"))
GROUP BY Areas.Area_details, Species_names.species_common_name
PIVOT Format([Date],"mmm-yy") In
("Oct-06","Nov-06","Dec-06","Jan-07","Feb-07");

Cheers... Sean

 

[Guest]

Sean,

What do you get when you remove the IN clause from the Pivot?

Your where clause and the in clause of your PIVOT are redundant. The IN
clause not only orders the output columns, but also filters the data, so you
can remove your WHERE clause.

What do you get when you delete the WHERE clause and the IN clause? Do you
actually see columns with the labels you are looking for?

Dale

 

[Guest]

Hi Dale
I got rid of the WHERE clause and that fixed it. Sorry for slow reply, got
dragged into other jobs.

Cheers... Sean

Sean,

What do you get when you remove the IN clause from the Pivot?

Your where clause and the in clause of your PIVOT are redundant. The IN
clause not only orders the output columns, but also filters the data, so you
can remove your WHERE clause.

What do you get when you delete the WHERE clause and the IN clause? Do you
actually see columns with the labels you are looking for?

Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi
I have a CrossTab Qry with 2 Rows and Columns with a dates:
"Oct-06","Nov-06","Dec-06","Jan-07","Feb-07"
I have put the above dates in the Properties/ColumnHeadings dialogue, but
get no headings...?
SQL: TRANSFORM Sum(Species_caught.Num_kept) AS SumOfNum_kept
SELECT Areas.Area_details, Species_names.species_common_name
FROM Areas INNER JOIN ([Trip Record] INNER JOIN (Species_names INNER JOIN
Species_caught ON Species_names.Species_code = Species_caught.Species_code)
ON [Trip Record].Trip_ID = Species_caught.Trip_ID) ON Areas.Area_Code = [Trip
Record].Area_code
WHERE (((Format([Date],"mmm-yy"))="Oct-06" Or
(Format([Date],"mmm-yy"))="Nov-06" Or (Format([Date],"mmm-yy"))="Dec-06" Or
(Format([Date],"mmm-yy"))="Jan-07" Or (Format([Date],"mmm-yy"))="Feb-07"))
GROUP BY Areas.Area_details, Species_names.species_common_name
PIVOT Format([Date],"mmm-yy") In
("Oct-06","Nov-06","Dec-06","Jan-07","Feb-07");

Cheers... Sean