Crosstab query with same VALUE in multiple columns

[David Fenton]

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

 

[ChrisJ]

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

 

[Davdi Fenton]

Sorry ChrisJ, my news server seems to have removed your message too
early. Can you please re-post?
Cheers
David
Australia