Major Crosstab

[Gerhard]

Hi there, I need a little help please.

I need to create the following report for a medical training school:

Course Name:..............................Course1........ .......................Course2
Subject Name:............Subject1....Subject2....Subject3. ......Subject4..Subject5
Date:...........................1/1..2/1....1/1..2/1..5/1....8/1..9/1............1/1......13/1..2/2
Periods for Student1:...5.....2.......1.....1.....8.......5... .2...............0...........3.....1
Periods for Student2:...5.....2.......3.....1.....8.......5... .2...............2...........3.....1
Periods for Student3:...0.....2.......3.....0.....8.......5... .2...............2...........3.....1
Periods for Student4:...5.....2.......3.....1.....0.......5... .2...............2...........0.....1

Background:
Input data comes from a register, keeping record of:
what subject was covered during each period of each day;
what students attended each of those periods.

The report is supposed to show the total number of periods that each student spent on each subject per day. The data is thus Sorted and Grouped by Course, Subject, Date and sum([periods]).

Problem:
The report needs to show the Course, Subject, Date as column headers and the Students' detail as row headers.
However, I cannot use the Course, Subject and Date as column headers in the crosstab query, since there might easily be more than 255 columns in the crosstab query - depending on the number of days to be included.

I am thus forced to use the student's details as column headers (since you will not have more that 255 students in a class) and to use the Course, Subject and Date detail as row headers.

How do I then print the data with the Course, Subject, Date detail as column headers and the Students as row headers?

At the moment I export to Excel; transpose the data and then print it in Excel. But this takes ages and you must do it over each time they need updated reports.

Any suggestions please???

[nico5038]

This will require some tricking.
Create a field that's combining the Course, Subject, Date and prepares the crosstable fieldslike:

Select StudentID as Row, Course & " " & Subject & " " & Date as Header, 1 as CountValue from tblX
WHERE Date between [StartDate] and [Enddate];
 

You should test that the number of distinct "Header" values don't exceed 255 (better not > 150) to prevent dumps. (More Header rows would just imply more reports for more periods).

This query can be used as the basis for a crosstable query, but I did found out sometimes the parameter request fails :-(
In that case we'll need to create the query from code. How are your VBA skills ?

When you need this in a report a next set of (VBA) tricks will be needed...

Nic;o)

P.S. Best not to use [Date] as a fieldname as it's also an Access function....

[Gerhard]

Hi Nico.

Let me get this straight...

1. Are you saying that I don't have a choice, but to use the Course, Subject, Date combination as column header? In other words,,, there is no way to transpose the data in Access or VB.

2. So this means the report has to run several times until all Dates have been included. How would you do this?

3. In the report itself, how do I check the value of the column headings. E.g. if I use the CourseID in the column heading, how do I extract it in the report - so that I can use it to look up the Course's description as well?

Regards
Gerhard

[nico5038]

Hi Gerhard

The response is marked by ==>""

1. Are you saying that I don't have a choice, but to use the Course, Subject, Date combination as column header? In other words,,, there is no way to transpose the data in Access or VB.
==> Access will only allow ONE field to be used for the header column, thus the concatenation is the only way to combine them.

2. So this means the report has to run several times until all Dates have been included. How would you do this?
==> Only as many times as you need periods of some 150 days to cover the needed period. Personally I would probably create monthly reports per Course, thus limiting the width. A 250 column reports doesn't look pritty...

3. In the report itself, how do I check the value of the column headings. E.g. if I use the CourseID in the column heading, how do I extract it in the report - so that I can use it to look up the Course's description as well?
==> The concatenated field becomes the column heading, thus the value should show as required. Problem will be the assignment of the column to the query field, as Access will name the fields after the column value.
Thus VBA code is needed to place the fields "relatively"

Nic;o)

[Gerhard]

Hi Nico

The response is marked by ***>""

3. In the report itself, how do I check the value of the column headings. E.g. if I use the CourseID in the column heading, how do I extract it in the report - so that I can use it to look up the Course's description as well?
==> The concatenated field becomes the column heading, thus the value should show as required. Problem will be the assignment of the column to the query field, as Access will name the fields after the column value.
Thus VBA code is needed to place the fields "relatively"

***> I cant use the Course Description as column heading since it is to long. I must thus use the CourseID and use it in the report to retrieve the Course Description.
How do I refer to the column name in the report in order to retrieve the CourseID?

Gerhard

[nico5038]

You'll need some code for that. I used the following code myself in a similar case. To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
 
Dim rs As Recordset
 
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
 
'Place headers
For intI = 1 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI
 
'Place correct controlsource
For intI = 13 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI
 
End Sub
 

The report query has one rowheader column, therefor the first field is effectively column 1 (count starts at 0) but it could differ for you when using multiple rowheaders.
Getting the Course description can be done in the "header loop" with a DLOOKUP() function.

Nic;o)

[Gerhard]

Thanx Nico.

That will help.

Regards
Gerhard

[nico5038]

Keep me posted and don't hesitate to ask when you get stuck :-)

Nic;o)