By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 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.

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

Thank you!

Best Regards,
Jun 15 '12 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 10K+
P: 12,366
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
  3. SELECT [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, [ESU Summary Report].cPrgmType, Sum([ESU Summary Report].nDuration) AS SumOfnDuration
  5. FROM [ESU Summary Report]
  7. WHERE ((([ESU Summary Report].cPrgmType)="ESU"))
  9. GROUP BY [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, [ESU Summary Report].cPrgmType
  11. PIVOT [ESU Summary Report].nSessionType
================================================== ======================================

thank you

Jun 16 '12 #3

Expert Mod 10K+
P: 12,366
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,
Jun 16 '12 #5

Expert Mod 10K+
P: 12,366
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!

Jun 17 '12 #7

Post your reply

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