Query correct, report not

[Theresa D via AccessMonster.com]

Hello,
I am using Access 2002, but have a db that is 2000 file format. I have
a query that works correctly, but when my boss used the wizard to create a
report from it, the detail level was not correct. The query takes fields
from 3 tables. The first table is just a course number (the primary key) and
name. The second table has course num and session num as the primary key,
plus info about that session. The third table has the course num, session
num, and facilitator as primary key, but no other fields. There can be many
sessions for each course and many facilitators for each session (the
relationships are set up correctly, with all of the referential integrity
boxes checked, if that makes a difference). The report is grouping on the
course name and session number. The only thing in the detail level is the
facilitator names. The correct number of facilitators is being displayed for
each session, but it is always the same name, which is the one on the first
line of the datasheet view of the query. Here is the SQL from the query:
SELECT tblCourse.strCourseName, tblCourseSession.intSessionNo,
tblCourseSession.dteCourseStart, tblCourseSession.dteCourseEnd,
tblCourseSession.strVenue, tblCourseSessionFacil.strFacilName
FROM tblCourse INNER JOIN (tblCourseSession INNER JOIN tblCourseSessionFacil
ON (tblCourseSession.intSessionNo = tblCourseSessionFacil.intSessionNo) AND
(tblCourseSession.intCourseNo = tblCourseSessionFacil.intCourseNo)) ON
tblCourse.intCourseNo = tblCourseSession.intCourseNo
ORDER BY tblCourse.intCourseNo, tblCourseSession.intSessionNo,
tblCourseSessionFacil.strFacilName;

What can I do to get the report to display the facilitators correctly?
I can't change the file format of the db, as it is for the governement of a
Sub-Saharan country. They are not exactly on the cutting edge of technology.

TIA,
Theresa

 

[Allen Browne]

Theresa, the data structure sounds fine. The query looks fine too (other
than that it would not return anything for courses have no sessions or
facilitators defined.) You say all that works, so the problem must be with
the report.

1. Create a new report. In the first dialog, choose the name of this query,
and go to Design View instead of the wizard.

2. In report design view, if you do not see the Field List, choose Field
List from the View menu. If you not see the Sorting And Grouping dialog,
choose Sorting And Grouping on the View menu. You should now have the report
in design view, with both these boxes floating over it.

3. On the first line of the Sorting and Grouping dialog, choose the Course
Name field.

4. On the next line of the dialog, choose the Course Number field.
In the lower pane of the dialog, set Group Header to Yes.
Access adds a grey bar for Course Number Header.
Add the Course Name to this section (by dragging from the Field List
dialog.)

5. On the next line of the dialog, choose the Session Number field.
Set Group Header to Yes.
Add the Session Number field to this section.

6. Add the Facilitator field to the Detail section.

That was quicker that using the Wizard anyway?

 

[Theresa D via AccessMonster.com]

Allen,
I recreated the report following your directions using design view this
time, but with the same results. The person we're creating this for didn't
actually ask for any reports, but we thought it would be easy. I have to go
install it this morning, though. I guess if she requests reports later, we
can always try something else.

Thanks, anyway!
Theresa