All rows don't show in report

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

I don't do a lot of reports, so this may be really simple minded....

I have a report based on a query. The query looks like this:

SELECT tblStudents.id, tblLevels.Levels, tblLevels.Level_Comment,
tblScores.Comments
FROM (tblStudents INNER JOIN tblLevels ON tblStudents.id = tblLevels.id)
LEFT JOIN tblScores ON (tblLevels.id = tblScores.id)
WHERE (((tblScores.Comments) Is Not Null))
ORDER BY tblStudents.id DESC;


.... and when it is executed, I get rows looking something like this
(tblScores is a multiple entry secondary to tblLevels):

[id] [levels] [level_comment] [comments]
1 1 'comment 1' 'another comment'
1 1 'comment 1' 'and yet another commnet'
1 1 'comment 1' 'and on and on ...'
2 3 'comment 2' 'this ones first comment'
2 3 'comment 2' 'the second comment'
3 5 'comment 3' 'the first comment of # 3'
3 5 'comment 3' 'the second comment of #3'


The format of the report looks like this (detail section).

"Student" "Level" "Comments"
[id] [levels] [level_comment]
[comments]

I have put id, levels and level_comment in the "sorting and grouping" list
with "group on" = "each value", but that didn't seem to make any difference.

I was hoping to suppress repeats for the first four fields - but I hadn't
gotten that far.

My problem is that only one row prints per id. So for this query result, I
would get the following:

Student Level Comments

1 1 Comment 1
another comment
2 3 comment 2
the second comment
3 5 comment 3
the first comment of #3


What has happened to the other rows in the query?
 
I'm not seeing how you can tell the first three rows in your example apart
from one another, except on the basis of the comment itself ... so what
happens when you have more than one "identical" comment?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
They should appear twice. There would really be two comments (from
different periods in the school day) and it would only be a coincidence that
they were the same. In the final report, I'll probably add the period name
to the display.
Jeff Boyce said:
I'm not seeing how you can tell the first three rows in your example apart
from one another, except on the basis of the comment itself ... so what
happens when you have more than one "identical" comment?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Laurel said:
I don't do a lot of reports, so this may be really simple minded....

I have a report based on a query. The query looks like this:

SELECT tblStudents.id, tblLevels.Levels, tblLevels.Level_Comment,
tblScores.Comments
FROM (tblStudents INNER JOIN tblLevels ON tblStudents.id = tblLevels.id)
LEFT JOIN tblScores ON (tblLevels.id = tblScores.id)
WHERE (((tblScores.Comments) Is Not Null))
ORDER BY tblStudents.id DESC;


... and when it is executed, I get rows looking something like this
(tblScores is a multiple entry secondary to tblLevels):

[id] [levels] [level_comment] [comments]
1 1 'comment 1' 'another comment'
1 1 'comment 1' 'and yet another commnet'
1 1 'comment 1' 'and on and on ...'
2 3 'comment 2' 'this ones first comment'
2 3 'comment 2' 'the second comment'
3 5 'comment 3' 'the first comment of # 3'
3 5 'comment 3' 'the second comment of #3'


The format of the report looks like this (detail section).

"Student" "Level" "Comments"
[id] [levels] [level_comment]
[comments]

I have put id, levels and level_comment in the "sorting and grouping"
list with "group on" = "each value", but that didn't seem to make any
difference.

I was hoping to suppress repeats for the first four fields - but I hadn't
gotten that far.

My problem is that only one row prints per id. So for this query
result, I would get the following:

Student Level Comments

1 1 Comment 1
another comment
2 3 comment 2
the second comment
3 5 comment 3
the first comment of #3


What has happened to the other rows in the query?
Click to expand...
Click to expand...
 
Laurel

I'd guess that one/more controls or sections have the Hide Duplicates
property turned on.

And unless you include that "period" value, and sort by it, Access gets to
decide which order to show them in.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Laurel said:
They should appear twice. There would really be two comments (from
different periods in the school day) and it would only be a coincidence
that they were the same. In the final report, I'll probably add the
period name to the display.
Jeff Boyce said:
I'm not seeing how you can tell the first three rows in your example
apart from one another, except on the basis of the comment itself ... so
what happens when you have more than one "identical" comment?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Laurel said:
I don't do a lot of reports, so this may be really simple minded....

I have a report based on a query. The query looks like this:

SELECT tblStudents.id, tblLevels.Levels, tblLevels.Level_Comment,
tblScores.Comments
FROM (tblStudents INNER JOIN tblLevels ON tblStudents.id = tblLevels.id)
LEFT JOIN tblScores ON (tblLevels.id = tblScores.id)
WHERE (((tblScores.Comments) Is Not Null))
ORDER BY tblStudents.id DESC;


... and when it is executed, I get rows looking something like this
(tblScores is a multiple entry secondary to tblLevels):

[id] [levels] [level_comment] [comments]
1 1 'comment 1' 'another comment'
1 1 'comment 1' 'and yet another commnet'
1 1 'comment 1' 'and on and on ...'
2 3 'comment 2' 'this ones first comment'
2 3 'comment 2' 'the second comment'
3 5 'comment 3' 'the first comment of # 3'
3 5 'comment 3' 'the second comment of #3'


The format of the report looks like this (detail section).

"Student" "Level" "Comments"
[id] [levels] [level_comment]
[comments]

I have put id, levels and level_comment in the "sorting and grouping"
list with "group on" = "each value", but that didn't seem to make any
difference.

I was hoping to suppress repeats for the first four fields - but I
hadn't gotten that far.

My problem is that only one row prints per id. So for this query
result, I would get the following:

Student Level Comments

1 1 Comment 1
another comment
2 3 comment 2
the second comment
3 5 comment 3
the first comment of #3


What has happened to the other rows in the query?
Click to expand...
Click to expand...
Click to expand...
 
It sounds like you might have put all the bound controls in a group header
(or possibly footer). I'd have thought you'd want to group the report on id,
Levels and Level_Comment with a group header for the Level_Comment group.
The controls bound to the id, Levels and Level_Comment fields would go in
this group header and the controls bound to the Period Name (when you’ve
added it to the query) and Comments fields would go in the detail section.

That way you'd see single values of each identical set of id, Levels and
Level_Comment values and below them one or more values of Period name and
Comments for the group. If you want the first comment for each group to be
on the same line as the id, Levels and Level_Comment values then you can out
the following in the group header's Format event procedure:

MoveLayout = False

and position the Period Name and Comments controls so they are to the right
of the controls in the group header.

BTW don't include an ORDER BY clause in the query; do the sorting in the
report.

Ken Sheridan
Stafford, England

Laurel said:
They should appear twice. There would really be two comments (from
different periods in the school day) and it would only be a coincidence that
they were the same. In the final report, I'll probably add the period name
to the display.
Jeff Boyce said:
I'm not seeing how you can tell the first three rows in your example apart
from one another, except on the basis of the comment itself ... so what
happens when you have more than one "identical" comment?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Laurel said:
I don't do a lot of reports, so this may be really simple minded....

I have a report based on a query. The query looks like this:

SELECT tblStudents.id, tblLevels.Levels, tblLevels.Level_Comment,
tblScores.Comments
FROM (tblStudents INNER JOIN tblLevels ON tblStudents.id = tblLevels.id)
LEFT JOIN tblScores ON (tblLevels.id = tblScores.id)
WHERE (((tblScores.Comments) Is Not Null))
ORDER BY tblStudents.id DESC;


... and when it is executed, I get rows looking something like this
(tblScores is a multiple entry secondary to tblLevels):

[id] [levels] [level_comment] [comments]
1 1 'comment 1' 'another comment'
1 1 'comment 1' 'and yet another commnet'
1 1 'comment 1' 'and on and on ...'
2 3 'comment 2' 'this ones first comment'
2 3 'comment 2' 'the second comment'
3 5 'comment 3' 'the first comment of # 3'
3 5 'comment 3' 'the second comment of #3'


The format of the report looks like this (detail section).

"Student" "Level" "Comments"
[id] [levels] [level_comment]
[comments]

I have put id, levels and level_comment in the "sorting and grouping"
list with "group on" = "each value", but that didn't seem to make any
difference.

I was hoping to suppress repeats for the first four fields - but I hadn't
gotten that far.

My problem is that only one row prints per id. So for this query
result, I would get the following:

Student Level Comments

1 1 Comment 1
another comment
2 3 comment 2
the second comment
3 5 comment 3
the first comment of #3


What has happened to the other rows in the query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top