Crosstab Query in Report

[Jet1234]

Hello,

I'm new to crosstab queries and simply not getting anywhere on my own. In the attached image, I've been able to get the Red cells working in an Access crosstab query. What I would like shown in the report is the Green cells. Essentially remove any spaces, this would also apply if the student was not taking any courses in a Term.



Any ideas, or should I try a new approach?

Thanks for any help!

[June7]

A crosstab is not going to accomplish what you want.

Post example of raw data source.

[Jet1234]

Hey June, thanks for getting back so fast.

The Primary key is StudentID and the foreign key is Program ID. The database is huge but this piece should be enough.

Thanks for any info!

Jet

[June7]

Why is ProgramYear in both tables?

I should have said a CROSSTAB alone will not accomplish. Consider:

Query1: OOS_Program
SELECT Program.StudentID, OOSMap.CourseID, OOSMap.ProgramYear
FROM Program INNER JOIN OOSMap ON Program.ProgramID = OOSMap.ProgramID;

Query2:
TRANSFORM First(CourseID) AS FirstOfCourseID
SELECT StudentID, DCount("*","OOS_Program","StudentID=" & [StudentID] & " AND ProgramYear='" & [ProgramYear] & "' AND CourseID<'" & [CourseID] & "'")+1 AS GrpSeq
FROM OOS_Program
GROUP BY StudentID, DCount("*","OOS_Program","StudentID=" & [StudentID] & " AND ProgramYear='" & [ProgramYear] & "' AND CourseID<'" & [CourseID] & "'")+1
PIVOT ProgramYear;

Be aware this query will be very slow with large dataset. Alternative is VBA code writing records to a 'temp' table.

[Jet1234]

Wow, you really saved me on this one. I never would have thought to do it that way on my own.

Much appreciated!!!

[Micron]

@jet1234 - it seems you are using your Excel brain to design in Access. The former is column based, the latter is row based. If you continue to develop in Access that way, you will continue to struggle.

[June7]

@Micron, did you look at the db? I do think the tables are normalized but OP had a requirement to display data in non-normalized arrangement, which is what CROSSTAB is intended for.

[Micron]

No; maybe I misunderstood the pictures. They appear to show what now seems to be a desire to build a multi-column report but I took it the way I did because the each season repeats every year. You seem to have helped out regardless if the db is normalized or not.

@jet1234 - apologize if the comment was unwarranted.