[AccessD] Advice on a table transformation

Gustav Brock gustav at cactus.dk
Fri Aug 20 14:11:31 CDT 2004


Hi Arthur

How is this different from a crosstab?
Seems a perfect match to me ...

/gustav


> 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.




More information about the AccessD mailing list