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

Hello database gurus and developers.
I have a problem that I cannot seem to solve...

Picture a spreadsheet something like the following:

                                                                     2004
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 consecutive 
dates in it, the next day it might have 23 consecutive dates in it. The 
query must work for all dates in the table.

Sample data in table 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