Report layout question

[Epoh Rio]

I am working on a database that logs a 12 week class.

Students that take the class can start at anytime

This is how I want the (sub)report to look (in a row to take up less space).

Lessons taken to date (StudentID):

1 2 3 4 5 6 7 8 9 10 11 12
x x x x x

Each time a student completes the class the class is recorded like this:

Table: [Attendance]

Fields: studentID, lessonDate, lessonNo, instructor, location

Sample: 25, 5/1/08, 3, 1, 1
30, 5/1/08, 3, 1, 1
35, 5/4/08, 4, 1, 1
25, 5/4/08, 4, 1, 1
40, 5/8/08, 5, 1, 1
25, 5/8/08, 5, 1, 1

This is how the report should look:

Lessons taken to date (25):

1 2 3 4 5 6 7 8 9 10 11 12
x x x

Thank you

 

[John Spencer]

Sounds as if you need to base the report on a cross-tab query. The SQL for
the query maight look something like the following.

TRANSFORM IIF(LessonDate<=Date,"X","") as Value
SELECT StudentID
FROM Attendance
WHERE LessonDate <= Date()
GROUP BY StudentID
PIVOT LessonNo IN (1,2,3,4,5,6,7,8,9,10,11,12)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Use a crosstab query to feed your report.

TRANSFORM IIF([lessonNo] Is Not Null, "X",) AS Expr1
SELECT studentID
FROM YourTable
GROUP BY studentID
PIVOT lessonNo IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

 

[Erez Mor]

hi
i solve this using a crosstab query
where the row header is the studentID and the column header is the
lessonNumber (if you cant this of what to put in the middle, choose Count or
First functions)
this way you can have 1 or all students listed with 1 row each, detailing
the lessons
if you want to force the query to show all lesson's columns (the default
behaviour is only lessons that were taken), open the query in sql view, go to
the last line beginning with "Pivot", and add this text before the semi-colon
"... In(1,2,3,4,5,6,7,8,9,10,11,12)"
this way all lessons are displayed, not only taken ones

this query will show something like
Student 1 2 3 4 5 6 7 8 9 10 11 12
Erez Mor 1 1 1 1

the 1's are the times i took each lesson (usualy 1 or none)
if you need to show an "x" instead, use the control source of the text box
on the report itself as "=iif(isnull([1],'','x')" - and do that for all 12
text boxes

hope it all makes sense to you
good luck
Erez

 

[Epoh Rio]

Awesome, I love it. That worked great!!!

Use a crosstab query to feed your report.

TRANSFORM IIF([lessonNo] Is Not Null, "X",) AS Expr1
SELECT studentID
FROM YourTable
GROUP BY studentID
PIVOT lessonNo IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);

--
KARL DEWEY
Build a little - Test a little

I am working on a database that logs a 12 week class.

Students that take the class can start at anytime

This is how I want the (sub)report to look (in a row to take up less space).

Lessons taken to date (StudentID):

1 2 3 4 5 6 7 8 9 10 11 12
x x x x x

Each time a student completes the class the class is recorded like this:

Table: [Attendance]

Fields: studentID, lessonDate, lessonNo, instructor, location

Sample: 25, 5/1/08, 3, 1, 1
30, 5/1/08, 3, 1, 1
35, 5/4/08, 4, 1, 1
25, 5/4/08, 4, 1, 1
40, 5/8/08, 5, 1, 1
25, 5/8/08, 5, 1, 1

This is how the report should look:

Lessons taken to date (25):

1 2 3 4 5 6 7 8 9 10 11 12
x x x

Thank you