[Accessd] export to excel

Hi Pedro

> thanks for your script. It works for the percentages of the cells,
> but i don't get in exp. Total  a percentage. Its still gives a sum
> of the cells. Is it possible that this field shows the total of the
> percentage (100%)
> so that i can see that i work with percentages.

Well, as it will always show 100, just insert a column with a fixed
expression:

 ...
 SELECT
   Patient,
   SUM([Countings]) AS Total,
   100 AS TotalPercent
 FROM
 ...

If you don't want to display the Total column, you'll need to feed the
query to a simple select query where you select only those columns to
be displayed.

/gustav


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