Cross tab: Multiple Rows/Values

[Access_Help]

Is there any way of producing a crosstab query with multiple value fields?

I'm producing a report that will list the subjects for each day of the week by period. At the moment it is only pulling one set of values from the query. E.g. it is only showing Science and Not maths which is also timetabled at the same time

I want an Access report designed so that it can be produced with no manual manipulation. I don't want to create multiple cross tab queries as that would be too much work for all the subjects

Here is the SQL, I am not an SQL programmer so would need help to edit this code or find a quick solution around it.

TRANSFORM Max(tbl_block_assignment.Block) AS MaxOfBlock
SELECT tbl_block_assignment.Period
FROM tbl_Blocks INNER JOIN tbl_block_assignment ON tbl_Blocks.Block = tbl_block_assignment.Block
GROUP BY tbl_block_assignment.Period
PIVOT tbl_block_assignment.Day;

 

[theDBguy]

Hi,


Just a guess but if you want to see multiple values, you could try making them as one. For example, perhaps you can concatenate them. If you show us some sample data, maybe we can see if it's possible or not.

 

[mike60smart]

Hi

If you have your tables normalised you can usually create a Group By Report which will give you all required info.

Can you upload a zipped copy of the Db with no confidential data so we can see the structure?

 

[Access_Help]

Thanks guys, attached is a zipped copy of the database.

Mike- Will try your suggestion
The DBGuy - not sure how to concatenate the values in a cross tab

 

[isladogs]

You can indeed create a report to show what you want.
There are two such reports attached.

It would be much harder to do so in a query.
The simplest method would probably be to concatenate values looping through a recordset but your table structure will make that harder than it needs to be

If you are going to take the timetable database further, you really should modify your design to actually use a fully normalised table design. At the moment, you have several tables but make no use of any except the first.

 

[Access_Help]

thanks isladogs

I achieved a similar report to yours, Is there any method to create a report that looks like a cross-tab (with the column heading and row headings and multiple values)

 

[isladogs]

Only with fairly complex coding as already suggested.
Before dong so, I would look carefully at the table structure.

If this is for a real world school timetable, you will have several year groups each of which will have several classes on at once. It is likely you would also want to show the teacher and room for each class in each of the teaching slots.
In a secondary school of 1500 students with 5 year group, there would typically be 60-70 classes for each teaching period.

I use complex vba to convert normalised tables into timetables for each student, teacher, room, subject, faculty etc. Each timetable slot shows the class name, subject, teacher, room and where applicable may indicate if there is support provided in a particular lesson.

I do also have reports listing all classes timetabled for a particular lesson listed by year group - the code needed involves the use of several user defined functions and has to be configured to work no matter what timetable structure each school uses. It is complicated!

 

[Access_Help]

Thanks isladogs. I am currently only working on the blocks, once this is done I can assign the classes rooms etc

I have found a temporary solution, adding a search field in the cross tab allows me to display more than one value!

 

[isladogs]

Does that mean you are writing the school timetable in Access?

Not sure I understand how you are using search with crosstab.
Good luck with the rest of your project.

 

[mike60smart]

Hi

As Colin and I have suggested you need to sort out your tables before progressing further.

If you are dealing with Blocks I would suggest that on a given Day you have a number of Periods and Each Period has a number of Sessions.

I always advocate having a Primary Key Autonumber in every table.