Cross tab - complex IIF

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
 
W

Wayne-I-M

ooops

that should have been

But if someone books in December "2008" for a trip in 2010 the booking is
shown in the december 09 column - as there is no "previous" column
 
W

Wayne-I-M

It's OK - I got it.

In case anyone is trying to do the same - replace the booking date in the
main query with the results of the sub.

Create a sub query and use
BookingMonth: IIf(DatePart("YYYY",[tblBookings]![BknBooking
Date])>=(DatePart("YYYY",[tblEvents]![EVTStart])-1),DatePart("M",[tblBookings]![BknBooking Date]),1)

then base the main query on this

:)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top