Generating summary reports from 2 tables with multiple calculations

[knoty]

Hi anyone,

I need help here!

Client Table
1. ClientId - Autonumber (Primary Key)
2. Client name - text

Session Table
1. SessionId - Autonumber (Primary Key)
2. sCid - Number (Foreign Key refers to Client.ClientId)
3. SessionType - Text (values = "T1"/"T2"/"T3"/"T4")
4. SessionDuration - number
5. SessionDate - Date

I need to create a report that looks like this

Client Name - T1/duration - T2/duration - T3/duration - T4/duration
client 1
client 2

Basically showing each client and how many sessions and how much time he spent on each session type.
i.e.Count(Sessiontype)/Sum(duration)


Is this even possible? Please enlighten me.
Anyone who has a solution, please help!

Thank you!



Best Regards,
knoty

[Rabbit]

Use a crosstab query.

[knoty]

Thanks Rabbit for the tip,

However, I have not been able to achieve complete results but here is what I got.
I only managed to get this using the Crosstab_wizard

cRegNumber - cName - cPrgmType - SumOfnDuration - FI - IS

2012-06-01 - Gregory - ESU - 2 - 1 - 2

The sql code generated is attached below. Hope you can advice me how to tweak it such that it gives the duration for each session type. [ESU Summary Report] is a Select query to merge the 2 tables that access requires in order to use the crosstab query.

================================================== ======================================

TRANSFORM Count([ESU Summary Report].nSessionNo) AS CountOfnSessionNo
 
SELECT [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, [ESU Summary Report].cPrgmType, Sum([ESU Summary Report].nDuration) AS SumOfnDuration
 
FROM [ESU Summary Report]
 
WHERE ((([ESU Summary Report].cPrgmType)="ESU"))
 
GROUP BY [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, [ESU Summary Report].cPrgmType
 
PIVOT [ESU Summary Report].nSessionType

================================================== ======================================


thank you

knoty

[Rabbit]

Please use code tags when posting code.

Why are you transforming on the count when you want the sum of the duration?

[knoty]

Thx Rabbit,

Sorry about the code tags.

I'm not a programmer and I actually don't understand the code very well.
I used the crosstab wizard to generate that code. I fiddled with the wizard but got stuck with the code in my previous post that came closest to what I was looking for.

What I need are 2 calculations as column headers.

1. Sum of Duration for each session type
2. Count of sessions for each session type

These must be in 2 seperate columns x 4 SessionTypes = total 8 columns, Grouped by client name

Apologies for my first post as it was misleading. I need a table with 9 columns which looks like this.

(Client Name) - (T1) - (sumT1duration) - (T2) - (sumT2duration) - (T3) - (sumT3duration) - (T4) - (sumT4duration)

(Client Name) = Name of client
(T1) = Count of T1 Sessions
(sumT1duration) = Sum of duration of T1 Sessions
(T2) = Count of T2 Sessions
(sumT1duration) = Sum of duration of T2 Sessions
(T3) = Count of T3 Sessions
(sumT1duration) = Sum of duration of T3 Sessions
(T4) = Count of T4 Sessions
(sumT1duration) = Sum of duration of T4 Sessions

Thank you for you patience.


Best regards,
knoty

[Rabbit]

You can't do two calculations in one cross tab. You will have to create two cross tabs and then join them together.

[knoty]

Ok thanks!

Solved!