[AccessD] Advice on a table transformation

Arthur:

Seems like code would be the fastest way.

Set up a table with as many fields as there are records in the current
table.  Create the first record in the new table as the dates of the old
table.

The first field in each record of the new table would be the field name of
the column in the old table.

Then loop through the first table sorting the data into the records of the
new table.  The report would then just be a mirror of the new table.

I use this 'temp table' approach all the time for complex reports instead of
fooling around trying to get complex queries to do it.

Rocky Smolin
Beach Access Software
http://www.e-z-mrp.com


----- Original Message ----- 
From: "Arthur Fuller" <artful at rogers.com>
To: "AccessD" <AccessD at databaseadvisors.com>
Sent: Friday, August 20, 2004 11:55 AM
Subject: [AccessD] Advice on a table transformation


> I have a table with a bunch of columns containing numeric values, most
> of them recording tons, a few recording values, and one recording the
> last day of a given month. There may be 10 years of data. Each month is
> represented by one row using its last day as the identifier. Then we
> have a bunch of columns whose values come from a collection of tables
> (i.e. sales figures, production figures, etc.). Some of the values are
> input by (alleged) humans.
>
> For example (these are just a few of said columns)
>
> Opening Stocks -- numeric
> Working Stocks -- stock on hand to cover emergencies (plant shutdown,
> worker strike, machine breakdown etc.)
> Production -- numbers derived from a table sent to my client by the
> producer (in this case a mine)
>
> There are a couple of dozen such columns.
>
> Now, to my question. I need to present this data in a sort-of
> spreadsheet format, wherein every column (save the date) is presented as
> a row, and the values are presented in the appropriate Year/Month
> column, so that the result looks like this:
>
>                   Jan 2005    Feb 2005    Mar 2005     etc.
> Opening Stocks    6843        6829        etc.
> Working Stocks    5500        5500        etc.
> Production       10572       10121        etc.
>
> I've been playing around with the crosstab wizard, at which I am no
> expert, but it doesn't seem to be giving me what I want. The PivotTable
> wizard is closer to what I need, and quite powerful, but I still can't
> get exactly what I want.
>
> Any ideas? Maybe I have to do it all in code? Or maybe I just need to
> learn more about either the crosstab or the pivot-table wizard?
>
> TIA,
> Arthur
>
> P.S.
> This is an Access MDB not a SQL BE.
>
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>