Access Report

[greggue]

Hello to everyone,
The formating of this post maybe off so here's a word attachement.

I am trying to create an access report that would group by number of course taken (3 max) and show which course(s) was taken in the columns next to the records as follow:
Detroit
New York
Miami
Group by 3
Night, Jennifer
c1
c1
c1
Group by 2
Smith, John
c1
c1
Doug, Youlog
c1
c1
Group by 1
Luther, Travis
c1


Here’s an example of the recordset returned by the query that pulls the data.

Course

LastName

FirstName

C_ID

C1 Detroit
Smith
John

111

C1 New York
Smith
John

111

C1 Detroit
Night
Jennifer

555

C1 New York
Night
Jennifer

555

C1 Miami
Night
Jennifer

555

C1 Detroit
Luther
Travis

888

C1 Miami
Doug
Youlog

999

C1 Detroit
Doug
Youlog

999

Help

[June7]

Required a series of queries.

CourseCount
SELECT [LastName] & ", " & [FirstName] AS StudentName, Count(Table3.C_ID) AS CountOfCourse
FROM Table3
GROUP BY [LastName] & ", " & [FirstName];

Query1
SELECT Left([Course],2) AS Code, Mid([Course],3) AS City, [LastName] & ", " & [FirstName] AS StudentName, Table3.C_ID
FROM Table3;

Query2
SELECT Query1.Code, Query1.City, Query1.StudentName, CourseCount.CountOfCourse, Query1.C_ID
FROM Query1 INNER JOIN CourseCount ON Query1.StudentName = CourseCount.StudentName;

Crosstab query
TRANSFORM First(Query2.[Code]) AS FirstOfCode
SELECT Query2.[CountOfCourse], Query2.[StudentName]
FROM Query2
GROUP BY Query2.[CountOfCourse], Query2.[StudentName]
ORDER BY Query2.[CountOfCourse] DESC , Query2.[StudentName]
PIVOT Query2.[City];

[NTC]

well - I believe that you have to make each its own crosstab report separately: Group by 3, Group by 2, Group by 1. 3 different reports - and then use one unbound report to hold these three as subreports.

its a bit of work. I'm not a fan of crosstab displays because of that point.