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

Hey Susan, 
Have you ever written an article on non-Cartesian joins? Gustav has a
perfect example here. I've used this technique in similar ways to add
specific dates to records. As far as I know, I've never seen this in any of
the Access mags. Be the first on your block!
Jim Hale

-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk]
Sent: Wednesday, June 23, 2004 9:24 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Multiple copies of value across columns in a
crosstab. RePost.


Hi David

You need two items: a table with dates and a table with your PAX data.
For simplicity I call them tblDate and tblPax.

Now, fill your tblDate with all the dates you wish to view or - if it
is filled with "every possible date from 1990 to 2030" - select the
date interval you wish to view.

Then create a crosstab query something like this:

<SQL>

  TRANSFORM
    Sum([PAX] * Abs([Date] Between [DateIn] And [DateOut]))
  SELECT
    tblPax.ID,
    tblPax.Name,
    tblPax.DateIn
    tblPax.DateOut
  FROM
    tblPax,
    tblDate
  GROUP BY
    tblPax.ID,
    tblPax.Name,
    tblPax.DateIn,
    tblPax.DateOut
  PIVOT
    tblDate.Date;

</SQL>

Note the missing join between the two tables.

/gustav


> 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

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com