Cross Tab Query Question

[Neil]

Hello All,

I have an enquiry table which keeps track of equipment quoted to a customer.
I have the following SQL statement:

TRANSFORM Count(qryForOverview.ID) AS CountOfID
SELECT qryForOverview.tblCompany.strCompanyName, Count(qryForOverview.ID) AS
[All Months]
FROM qryForOverview
GROUP BY qryForOverview.tblCompany.strCompanyName
PIVOT Format([DATE RECEIVED],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I got this by using the Crosstab Query wizard and have a couple of
questions:

1) Is it possible if no data exists in that month, to exclude the column
from the query?
2) If the is a null value to display a 0?
3) Are Jan 2004 and Jan 2005 results added together with this query? If so,
how can they be displayed separately?

I am thinking that some code may be required in order to do this and
appreciate that someone may have to go into great depths in order to respond
to my questions. If someone could just give me the general basic principles
on how to do the above, then I should be able to figure the exact details
out myself.

TIA,

Neil.

 

[Duane Hookom]

1) If you remove the Column Headings property value then the months with no
values will not display
2) You can use Val(Nz(Count(qryForOverview.ID),0)) AS CountOfID
3) You can add "Year([Date Received]) as TheYear" to the Selected fields
and
Year([Date Received]) in the Group By

BTW: Since this is for a report, do you really want to hide columns?

 

[Neil]

Hello Duane,

Thanks for your help. Changed the report as you requested and everything
looks good. I have also kept all the months in. When i thought about it,
there was no need to hide the columns.


Neil.

1) If you remove the Column Headings property value then the months with
no values will not display
2) You can use Val(Nz(Count(qryForOverview.ID),0)) AS CountOfID
3) You can add "Year([Date Received]) as TheYear" to the Selected fields
and
Year([Date Received]) in the Group By

BTW: Since this is for a report, do you really want to hide columns?
--
Duane Hookom
MS Access MVP

Hello All,

I have an enquiry table which keeps track of equipment quoted to a
customer.
I have the following SQL statement:

TRANSFORM Count(qryForOverview.ID) AS CountOfID
SELECT qryForOverview.tblCompany.strCompanyName, Count(qryForOverview.ID)
AS [All Months]
FROM qryForOverview
GROUP BY qryForOverview.tblCompany.strCompanyName
PIVOT Format([DATE RECEIVED],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I got this by using the Crosstab Query wizard and have a couple of
questions:

1) Is it possible if no data exists in that month, to exclude the column
from the query?
2) If the is a null value to display a 0?
3) Are Jan 2004 and Jan 2005 results added together with this query? If
so, how can they be displayed separately?

I am thinking that some code may be required in order to do this and
appreciate that someone may have to go into great depths in order to
respond to my questions. If someone could just give me the general basic
principles on how to do the above, then I should be able to figure the
exact details out myself.

TIA,

Neil.