W
Wayne-I-M
Hi
I have a cross tab query (thats getting more weird by the day).
The query gives the bookings per month for ski holidays but has a problem in
that if someone books a trip more than a year in advance (which many do) the
booking is added to the following year.
So
Jan Feb Mar - - - Nov Dec
12 34 56 222 333
This is fine
But if someone books in December 2009 for a trip in 2010 the booking is
shown in the december column - as there is no "previous" column
I have tried to add more to the initial IIF
Ski:
IIf(DatePart("yyyy",[tblEvents]![EVTStart])>DatePart("yyyy",[tblBookings]![BknBooking
Date]),DatePart("yyyy",[tblEvents]![EVTStart])-1,IIf(Month([tblEvents]![EVTStart])<8,DatePart("yyyy",[tblEvents]![EVTStart]),DatePart("yyyy",[tblEvents]![EVTStart])+1))
to look for booking made when the holiday start ([EVTStart]) is less than
the booking date ([BknBooking Date])
this works - sort off -but this adds another row just showing that booking -
and I am looking for another column not a row
I am trying to add another column (may not be possible) - to show booking
before the start of the year before the trip.
something like this (for the 2010 row) - the 2009 would be the column
heading for my imaginary new column
2009 Jan Feb Mar - - - Nov Dec
10 12 34 56 222 333
Any ideas ??? (I'm baffeled)
This is what I have at the moment which works (except for adding the 2009
December bookings to the December 2010)
TRANSFORM NZ(Count(tblBookings.[BknBooking Date]),0) AS [CountOfBknBooking
Date1]
SELECT
IIf(Month(tblEvents!EVTStart)<8,DatePart("yyyy",tblEvents!EVTStart),DatePart("yyyy",tblEvents!EVTStart)+1) AS Ski
FROM tblEvents INNER JOIN tblBookings ON tblEvents.EVTEventID =
tblBookings.EVTEventID
WHERE (((tblEvents.EVTCATID) Like "ski*"))
GROUP BY
IIf(Month(tblEvents!EVTStart)<8,DatePart("yyyy",tblEvents!EVTStart),DatePart("yyyy",tblEvents!EVTStart)+1), tblEvents.EVTCATID
PIVOT Format(tblBookings![BknBooking Date],"MMM") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Many thanks
I have a cross tab query (thats getting more weird by the day).
The query gives the bookings per month for ski holidays but has a problem in
that if someone books a trip more than a year in advance (which many do) the
booking is added to the following year.
So
Jan Feb Mar - - - Nov Dec
12 34 56 222 333
This is fine
But if someone books in December 2009 for a trip in 2010 the booking is
shown in the december column - as there is no "previous" column
I have tried to add more to the initial IIF
Ski:
IIf(DatePart("yyyy",[tblEvents]![EVTStart])>DatePart("yyyy",[tblBookings]![BknBooking
Date]),DatePart("yyyy",[tblEvents]![EVTStart])-1,IIf(Month([tblEvents]![EVTStart])<8,DatePart("yyyy",[tblEvents]![EVTStart]),DatePart("yyyy",[tblEvents]![EVTStart])+1))
to look for booking made when the holiday start ([EVTStart]) is less than
the booking date ([BknBooking Date])
this works - sort off -but this adds another row just showing that booking -
and I am looking for another column not a row
I am trying to add another column (may not be possible) - to show booking
before the start of the year before the trip.
something like this (for the 2010 row) - the 2009 would be the column
heading for my imaginary new column
2009 Jan Feb Mar - - - Nov Dec
10 12 34 56 222 333
Any ideas ??? (I'm baffeled)
This is what I have at the moment which works (except for adding the 2009
December bookings to the December 2010)
TRANSFORM NZ(Count(tblBookings.[BknBooking Date]),0) AS [CountOfBknBooking
Date1]
SELECT
IIf(Month(tblEvents!EVTStart)<8,DatePart("yyyy",tblEvents!EVTStart),DatePart("yyyy",tblEvents!EVTStart)+1) AS Ski
FROM tblEvents INNER JOIN tblBookings ON tblEvents.EVTEventID =
tblBookings.EVTEventID
WHERE (((tblEvents.EVTCATID) Like "ski*"))
GROUP BY
IIf(Month(tblEvents!EVTStart)<8,DatePart("yyyy",tblEvents!EVTStart),DatePart("yyyy",tblEvents!EVTStart)+1), tblEvents.EVTCATID
PIVOT Format(tblBookings![BknBooking Date],"MMM") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Many thanks