Major Crosstab - Microsoft Access / VBA Forum

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

Moderator

2,076 Posts

July 1st, 2008
07:57 PM
#2

Re: Major Crosstab

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

Expand|Select|Wrap|Line Numbers

	
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

Newbie

23 Posts

July 2nd, 2008
08:33 AM
#3

Re: Major Crosstab

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

Moderator

2,076 Posts

July 2nd, 2008
12:51 PM
#4

Re: Major Crosstab

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

Newbie

23 Posts

July 2nd, 2008
05:31 PM
#5

Re: Major Crosstab

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

Moderator

2,076 Posts

July 2nd, 2008
06:12 PM
#6

Re: Major Crosstab

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:

Expand|Select|Wrap|Line Numbers

	
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)