Hi David,
Rather than running an export for Access to Excel, and
then getting others to sum columns, try this single query
that should do what you require


SELECT TEMP_StudyTourCalendar.DayNumber, DSum
("[pax]","[qryST_Calendar]","[DateIn] =#" &
Format$([DayNumber],"dd mmm yyyy") & "# And [DateOut]=#"
& Format$([DayNumber],"dd mmm yyyy") & "#") AS [Total Pax]
FROM TEMP_StudyTourCalendar
ORDER BY TEMP_StudyTourCalendar.DayNumber;


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

Picture a spreadsheet something like the following - tabs
could muck
up the display a bit:


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) The remaining column headers come
from a single
field in a table that is created everyday from a query.
See sample
data further down.

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

.