column headings in CrossTab Qry?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Hi Dale
I got rid of the WHERE clause and that fixed it. Sorry for slow reply, got
dragged into other jobs.

Cheers... Sean

Dale Fye said:
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.


NZ_Sean said:
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
Click to expand...
Click to expand...
 
Back
Top