[AccessD] Multiple copies of value across columns in a crosstab. RePost.

Hello database gurus and developers.
Gustav requested re-post because spreadsheet sample was messed up. I hope 
it can be re-formatted with my asterisks this time. Just line 'em up. If 
this doesn't work can anyone suggest a way I can get the sample to the 
group with formatting intact? I can't attach a text file can I?

Anyway...
I have a problem that I cannot seem to solve.

Picture a spreadsheet something like the following (asterisks are column 
separators and should line up): The sample below should show 5 rows.

col1           *col2 
*col3      *col4          *col5   *col6     *col7     *col8    *col9 
*col10 *
Name         *PAX*DATEIN*DATEOUT *Jun-01 *Jun-02 *Jun-03 *Jun-04 *Jun-05 
*Jun-06*
Fred's 
Tours*12  *Jun-01   *Jun-05      *12       *12       *12       *12 
*12        *         *
Bob's 
Trips  *20  *Jun-03   *Jun-06      *          *           *20       *20 
   *20        *20     *
Mary's 
Tours*41  *Jun-02  *Jun-05      *          *41        *41       *41 
*41        *        *

I have data for the first four columns in a table in a database (Name, PAX, 
DATEIN, DATEOUT)

I can interrogate the database to find the min date in the DATEIN field and 
the max date in the DATEOUT field in the entire table. I use these two 
values to create a temporary table of dates from the min date to the max 
date. These dynamic values will become the column headers for a crosstab 
query where I am attempting to re-create the above spreadsheet.

I can get the PAX to appear in the correct column ONLY for the first date, 
but not EACH date in the range from DATEIN to DATEOUT (as shown by the 
spreadsheet sample). The idea is that the crosstab query will export to 
EXCEL and the user just has to sum each column to know how many PAX they 
are dealing with on a given date (eg. On Jun 1st they have 12 PAX, but on 
Jun 3rd they have to deal with 73 PAX, on Jun 6th only 20 PAX) As you can 
see the PAX is just copied from the PAX column. We never know what the 
minimum or maximum date will be on any day. The Crosstab query picks up the 
dates from the TEMP table (which just has one field of date type)

Below is the actual SQL that works, but only puts the PAX in the column 
where the *first* date column matches the DateIn for that Tour. I want the 
PAX number to appear in every column from the DateIn to the DateOut, as 
shown in the sample spreadsheet section above.

TRANSFORM First(qryST_Calendar.PAX) AS FirstOfConfirmedPAX
SELECT qryST_Calendar.TourName, qryST_Calendar.DateIn, 
qryST_Calendar.DateOut, qryST_Calendar.PAX
FROM qryST_Calendar RIGHT JOIN TEMP_StudyTourCalendar
ON qryST_Calendar.DateIn = TEMP_StudyTourCalendar.DayNumber
GROUP BY qryST_Calendar.TourName, qryST_Calendar.DateIn, 
qryST_Calendar.DateOut,
qryST_Calendar.PAX
PIVOT TEMP_StudyTourCalendar.DayNumber;

The TEMP_StudyTourCalendar.DayNumber is the field in the created table 
which holds all the dates from the lowest DateIn to the highest DateOut and 
forms the column headers. These will vary every day. We will only know the 
first date and the last date in the table called TEMP_StudyTourCalendar, on 
the day the crosstab query is run. One day it might have only 6 dates in 
it, the next day it might have 23 dates in it. The query must work for all 
dates in the table.

Sample data in TEMP_StudyTourCalendar for the spreadsheet above would be:
    DayNumber
    01-Jun-2004
    02-Jun-2004
    03-Jun-2004
    04-Jun-2004
    05-Jun-2004
    06-Jun-2004

Am I missing something simple here?
Any gurus like to sink their teeth into this one?

Regards
David Fenton
Brisbane
Australia