Nulls in a crostab query

[Wayne-I-M]

Hi

I have a simple crostab showing booking per year (row) and per month (column)

So should be

Jan Feb Apr Mar
0 5 34 0 etc etc

But the null are not showing - any ideas ?

Tried the most simple things I could think off but - not working
CountOfBknBooking Date1: Count(Nz([tblBookings]![BknBooking Date],0))
and
CountOfBknBooking Date1: Nz(Count([tblBookings]![BknBooking Date]),0)




TRANSFORM Count(tblBookings.[BknBooking Date]) AS [CountOfBknBooking Date1]
SELECT DatePart("yyyy",tblEvents!EVTStart) AS TripYear
FROM tblEvents INNER JOIN tblBookings ON tblEvents.EVTEventID =
tblBookings.EVTEventID
WHERE (((tblEvents.EVTCATID) Like "ski*"))
GROUP BY DatePart("yyyy",tblEvents!EVTStart), tblEvents.EVTCATID
PIVOT Format(tblBookings![BknBooking Date],"MMM");


Access 2k3 -

Many thanks

 

[John Spencer (MVP)]

Try the NZ on the outside of the count

TRANSFORM NZ(Count(tblBookings.[BknBooking Date]),0)
AS [CountOfBknBooking Date1]
SELECT DatePart("yyyy",tblEvents!EVTStart) AS TripYear
FROM tblEvents INNER JOIN tblBookings
ON tblEvents.EVTEventID = tblBookings.EVTEventID
WHERE (((tblEvents.EVTCATID) Like "ski*"))
GROUP BY DatePart("yyyy",tblEvents!EVTStart), tblEvents.EVTCATID
PIVOT Format(tblBookings![BknBooking Date],"MMM");

In Addition, you might modify the PIVOT clause to list the month abbreviations
in an In clause. That will return the months in the order you have entered
them AND will always report all 12 months, even if there are no records for
the specified month.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT Format(tblBookings![BknBooking Date],"MMM") IN
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Wayne-I-M]

Excellent - multo thank

--
Wayne
Trentino, Italia.

Try the NZ on the outside of the count

TRANSFORM NZ(Count(tblBookings.[BknBooking Date]),0)
AS [CountOfBknBooking Date1]
SELECT DatePart("yyyy",tblEvents!EVTStart) AS TripYear
FROM tblEvents INNER JOIN tblBookings
ON tblEvents.EVTEventID = tblBookings.EVTEventID
WHERE (((tblEvents.EVTCATID) Like "ski*"))
GROUP BY DatePart("yyyy",tblEvents!EVTStart), tblEvents.EVTCATID
PIVOT Format(tblBookings![BknBooking Date],"MMM");

In Addition, you might modify the PIVOT clause to list the month abbreviations
in an In clause. That will return the months in the order you have entered
them AND will always report all 12 months, even if there are no records for
the specified month.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT Format(tblBookings![BknBooking Date],"MMM") IN
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi

I have a simple crostab showing booking per year (row) and per month (column)

So should be

Jan Feb Apr Mar
0 5 34 0 etc etc

But the null are not showing - any ideas ?

Tried the most simple things I could think off but - not working
CountOfBknBooking Date1: Count(Nz([tblBookings]![BknBooking Date],0))
and
CountOfBknBooking Date1: Nz(Count([tblBookings]![BknBooking Date]),0)




TRANSFORM Count(tblBookings.[BknBooking Date]) AS [CountOfBknBooking Date1]
SELECT DatePart("yyyy",tblEvents!EVTStart) AS TripYear
FROM tblEvents INNER JOIN tblBookings ON tblEvents.EVTEventID =
tblBookings.EVTEventID
WHERE (((tblEvents.EVTCATID) Like "ski*"))
GROUP BY DatePart("yyyy",tblEvents!EVTStart), tblEvents.EVTCATID
PIVOT Format(tblBookings![BknBooking Date],"MMM");


Access 2k3 -

Many thanks