transform Data - please please help... its important

[december03]

I need to transform data from the access main and sub table for analysis

Main table

ID Seiz_Date Seiz_DateTx Seiz_DateYr
400 5 /1 /2006 aa 8
400 5 /2 /2006 vv 7
400 5 /13/2006 cc 5
400 6 /4 /2006 gg 3

transform to the format below

ID seix_Date1 seiz_Date2 seiz_Date3 seiz_date4 Seiz_DateTx1 Seiz_DateTx2 Seiz_DateTx3 Seiz_DateTx4 Seiz_DateYr1 Seiz_DateYr2 Seiz_DateYr3 Seiz_DateYr4
400 5 /1 /2006 5 /2 /2006 5 /13 /2006 6 /4 /2006 aa vv cc gg




or the format below....



ID seix_Date1 Seiz_DateTx1 Seiz_DateYr1 seiz_Date2 Seiz_DateTx2 Seiz_DateYr2 seiz_Date3 Seiz_DateTx3 Seiz_DateYr3 seiz_date4 Seiz_DateTx4 Seiz_DateYr4


where the No. of records for the ID can vary from 1 to 10

Please help its very necessary to do this tranformation.

[Stewart Ross]

...ID Seiz_Date Seiz_DateTx Seiz_DateYr
400 5 /1 /2006 aa 8
400 5 /2 /2006 vv 7
400 5 /13/2006 cc 5
400 6 /4 /2006 gg 3

transform to the format below

ID seix_Date1 seiz_Date2 seiz_Date3 seiz_date4 Seiz_DateTx1 Seiz_DateTx2 Seiz_DateTx3 Seiz_DateTx4 Seiz_DateYr1 Seiz_DateYr2 Seiz_DateYr3 Seiz_DateYr4
400 5 /1 /2006 5 /2 /2006 5 /13 /2006 6 /4 /2006 aa vv cc gg
...

Hi. I'm sorry to say there is no simple or easy way to do the transform you mention in Access itself.

You want to take the individual rows, each with an ID, a date, and another reference, and transform those rows into single rows containing two lists, the first being all the dates from the individual rows for that ID, and the second all the references for that ID. The only way I could see of doing this to give the result you are after is to process all records in a loop, generate the lists for each row as part of the loop processing, then output the IDs and the lists to Excel, say. This is a non-trivial programming task.

Within Access it is possible to use crosstab queries on an individual basis to provide lists of dates, or lists of the references, but unfortunately these would list all dates and all references from all rows, not just the ones for that ID alone.

As an example, with just two IDs here is some sample data transformed using a crosstab query on the date alone (all the dates are listed in the header line):

ID 06/04/2006 01/05/2006 02/05/2006 13/05/2006 03/05/2007 04/05/2007 05/05/2007 06/05/2007 07/05/2007 08/05/2007 09/05/2007
400 06/04/2006 01/05/2006 02/05/2006 13/05/2006     
500                                                                                     03/05/2007 04/05/2007 05/05/2007 06/05/2007 07/05/2007 08/05/2007 09/05/2007

Sorry not to be able to provide a suitable solution which would help.

-Stewart