By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Generating summary reports from 2 tables with multiple calculations

P: 4
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
Jun 15 '12 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,316
Use a crosstab query.
Jun 15 '12 #2

P: 4
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.

================================================== ======================================
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count([ESU Summary Report].nSessionNo) AS CountOfnSessionNo
  2.  
  3. SELECT [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, [ESU Summary Report].cPrgmType, Sum([ESU Summary Report].nDuration) AS SumOfnDuration
  4.  
  5. FROM [ESU Summary Report]
  6.  
  7. WHERE ((([ESU Summary Report].cPrgmType)="ESU"))
  8.  
  9. GROUP BY [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, [ESU Summary Report].cPrgmType
  10.  
  11. PIVOT [ESU Summary Report].nSessionType
================================================== ======================================


thank you

knoty
Jun 16 '12 #3

Rabbit
Expert Mod 10K+
P: 12,316
Please use code tags when posting code.

Why are you transforming on the count when you want the sum of the duration?
Jun 16 '12 #4

P: 4
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
Jun 16 '12 #5

Rabbit
Expert Mod 10K+
P: 12,316
You can't do two calculations in one cross tab. You will have to create two cross tabs and then join them together.
Jun 16 '12 #6

P: 4
Ok thanks!

Solved!
Jun 17 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.