Access 2002 crosstab query

[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)

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

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


Please enlighten me.
Anyone who has a solution, please help!

Thank you!



Best Regards,
knoty

[June7]

Review
https://www.accessforums.net/program...ias-21972.html
http://datapigtechnologies.com/flashfiles/crosstab.html
http://allenbrowne.com/func-concat.html
https://www.accessforums.net/queries...row-23882.html

Consider:

Query1
SELECT Session.sCid, Session.SessionType, Sum(Session.SessionDuration) AS SumOfSessionDuration, Count(Session.SessionID) AS CountOfSessionID, [SessionType] & "/" & "Duration" AS ColumnHead, Count([SessionID]) & "/" & Sum([SessionDuration]) AS CountTime
FROM [Session]
GROUP BY Session.sCid, Session.SessionType;

Query2
TRANSFORM First(CountTime) AS FirstOfCountTime
SELECT sCid
FROM Query1
GROUP BY sCid
PIVOT ColumnHead;

[knoty]

Hi June7,

Thank you for the prompt response! Appreciate it!

I tried one of your suggestions and managed to get something similar to what I am looking for with a slight problem that I realized was not clear on my part in my first post.

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
(sumT2duration) = Sum of duration of T2 Sessions
(T3) = Count of T3 Sessions
(sumT3duration) = Sum of duration of T3 Sessions
(T4) = Count of T4 Sessions
(sumT4duration) = Sum of duration of T4 Sessions

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

TRANSFORM Count([ESU Summary Report].nSessionNo) & "  -  " & Sum([ESU Summary Report].nDuration) AS CountSessionSumDuration
SELECT [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, Count([nSessionNo]) & "  -  " & Sum([nDuration]) AS Session_Hrs
FROM [ESU Summary Report]
GROUP BY [ESU Summary Report].cRegNumber, [ESU Summary Report].cName
PIVOT [ESU Summary Report].nSessionType;

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

I am able to get all the correct data calculations from the code above but just need to split

Count([ESU Summary Report].nSessionNo) & "  -  " & Sum([ESU Summary Report].nDuration) AS CountSessionSumDuration

into 2 columns as I need to export this to excel later on. Any way to do this?


Best Regards
knoty

[June7]

I should have seen this before. Sometimes things seem more complicated than they really are!

Try two crosstabs, one to sum the duration and one to count the session types.

Then join the crosstabs on the sCID.

[knoty]

Hey June7,

Ok, I've managed to get the 2 crosstabs to work perfectly but I'm not able to join them somehow. Access wizard keeps giving me error.
This is what I've managed to get so far.

Crosstab for CountSession

TRANSFORM Count([ESU Summary Report].nSessionNo) AS CountSessions
SELECT [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, Count([ESU Summary Report].[nSessionNo]) AS SessionByType
FROM [ESU Summary Report]
GROUP BY [ESU Summary Report].cRegNumber, [ESU Summary Report].cName
PIVOT [ESU Summary Report].nSessionType;

cRegNumber	cName	SessionByType	FI	GW	IS	NS
ESU-2012-03-01-H	Apple	4			4
ESU-2012-03-02-H	Ben	3	1		2
ESU-2012-03-03-H	Cody	3			3
ESU-2012-03-04-H	Daren	5	1		4

Crosstab for SumDuration

TRANSFORM Sum([ESU Summary Report].nDuration) AS SumDuration
SELECT [ESU Summary Report].cRegNumber, [ESU Summary Report].cName, Sum([nDuration]) AS DurationByType
FROM [ESU Summary Report]
GROUP BY [ESU Summary Report].cRegNumber, [ESU Summary Report].cName
PIVOT [ESU Summary Report].nSessionType & " / Hrs ";

cRegNumber	cName	DurationByType	FI / Hrs	GW / Hrs	IS / Hrs	NS / Hrs
ESU-2012-03-01-H	Apple	2			2
ESU-2012-03-02-H	Ben	2	1		1
ESU-2012-03-03-H	Cody	1.5			1.5
ESU-2012-03-04-H	Daren	3	1		2

Do I use a SELECT query or another Crosstab query to do the merging?
Please advise.

Thank you!
knoty

[June7]

What error are you getting?

Join the crosstabs with a SELECT query and link on the reg number.

[knoty]

Hi June7,

Error:
You have chosen fields from record sources which the wizard cannot connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only one table or only the query.

I get this error from using the Crosstab wizard and the Select wizard.

Seems I cannot choose fields from 2 crosstabs. hmm...

Any advice?

knoty

[June7]

I tested the procedure before offering suggestion. It did work.

Want to provide your db for analysis? Follow instructions at bottom of my post.

[knoty]

hi June7,

Thanks for your help so far.

I've attached the DB as requested.


Cheers,
knoty

[June7]

You are including cName as second RowHeader so the join must also link on that field:

SELECT [ESU Summary Report_CrosstabDurations].*, [ESU Summary Report_CrosstabSessions].SessionByType, [ESU Summary Report_CrosstabSessions].FI, [ESU Summary Report_CrosstabSessions].GW, [ESU Summary Report_CrosstabSessions].[IS], [ESU Summary Report_CrosstabSessions].NS
FROM [ESU Summary Report_CrosstabSessions] INNER JOIN [ESU Summary Report_CrosstabDurations] ON ([ESU Summary Report_CrosstabSessions].cName = [ESU Summary Report_CrosstabDurations].cName) AND ([ESU Summary Report_CrosstabSessions].cRegNumber = [ESU Summary Report_CrosstabDurations].cRegNumber);

Problem with this is the Sessions crosstab includes nDate as a Row Header for grouping but the Durations crosstab does not. So Sessions has more rows than Durations. This causes the Durations data to repeat.

The two cosstabs must have the same number of rows for this to work.

[knoty]

Hey June7,

Thanks! You have been very precise with your instructions and that has helped lots! I've managed to do it with your help!

Don't mind me asking something else here following relating to the nDate problem.

Is there a way to pass parameters into the [ESU Summary Report] query to select a range of dates when the final report is generated?

Currently the flow of queries is as such

1. [ESU Summary Report]

2. [ESU Summary Report_CrosstabSessions] + [ESU Summary Report_CrosstabDurations]

3. [3 months ESU Report] - (3 months report with variable start dates)

From my form interface, I would generate [3 months ESU Report], how should user go about entering a dynamic start and end date when the report is launched?
I can currently do this by hard coding the WHERE clause in the [ESU Summary report] but for users to do that would be a little hard.

Any way to prompt users when generating the [3 Months ESU Report]. Problem is that the crosstabs do not have the nDate field so I can't select it in the merged Select Query.


cheers!
knoty

[June7]

Instead of the hard-coded dates, options:

1. Query input parameter prompts. I never use this because can't validate user input.

2. Query refers to controls on form for input parameters.

[knoty]

Hi June7,

Thanks for your reply. Can you explain a little more on how to do that?

Instead of the hard-coded dates, options:

1. Query input parameter prompts. I never use this because can't validate user input.

2. Query refers to controls on form for input parameters.

I have a form with 2 textboxes that collect the start and end dates for the 3 month period and a button that launches the crosstab merging query but that query does not contain the nDate field.

How can I pass those values into the [ESU Summary Report] query that the crosstab queries use when I click the button?


knoty

[June7]

Criteria expression below the nDate field that references the controls on the form. The syntax would be (use your real names):

BETWEEN Forms!formname!startdate AND Forms!formname!enddate

[knoty]

Hi June7,

Sorry for the late reply.

Yes! I've tried that but somehow it doesn't seem to work. Error from Access is that the query is too complexed and access cannot reference the fields from the form. Could this be because of the multiple queries that are needed to generate the report?

Alternatively, I was wondering if I could change the SQL code in a query permanently so when I click a button on the form, I could update the SQL code in the query before running the report? This would make it a lot easier as I can just create buttons for each reporting period with the respective sql code. Is this possible?

Any advice on this?


knoty