[Accessd] export to excel

Hi Pedro

Something like this could be used:

<SQL>

TRANSFORM
  INT((SUM([Countings]) / [Total] * 1000) + 0.5) / 10
SELECT
  Patient,
  SUM([Countings]) AS Total
FROM
  tblPatientCell
GROUP BY
  Patient
PIVOT
  "Cell " & [Cell];

</SQL>

Note that INT() rounds down and 0.5 compensates for that.
If more sophisticated rounding is needed you'll have to use an
external function.

/gustav


> here is the structure of the table

> Patient   Cell    Count
> 001        A        15
> 001        A        10
> 001        B        20
> 002        A        5
> 002        A        10
> 002        A        5
> 002        B        10
> 002        B        20
> etc. etc.

> i need cross table

>           CellA         CellB      
> patient   sum (%)       sum (%)         Total (%)
> 001       25  (55,5%)   20  (44,5%)     45    (100%)
> 002       20  (40%)     30  (60%)       50    (100%)
> etc.

> only the percentage will do!

> Pedro Janssen




> From: Gustav Brock <gustav at cactus.dk>
> To: "pedro at plex.nl" <accessd at databaseadvisors.com>
> Date: Wed, 19 Feb 2003 12:41:06 +0100
> Subject: Re: [AccessD] export to excel

> Hi Pedro

> I think you need to tell a bit more to get useful help.

> Are you making one pivot table from data from 40000 patients?
> You can in Excel attach the data from Access without importing them.

> Or is it one pivot for each patient?
> Do you need a graph or the only the data?
> Could you make a sketch of the requested output?