Help! summarizing student attendance in report problem

[serviceman via AccessMonster.com]

Hi again gang!
I'm stuck in report hell, and I need some salvation...
I have a report based on a query that shows training classes for a given date
range and lists all of the students that attended each class. This part works
fine. What I would like to do is add a list(subreport?) to the bottom of the
report that shows all of the students in the main report with a total number
of classes that each student attended. This way I can do a count of the
number of classes/the number of classes attended and see if each student is
meeting required criteria. I AM COMPLETELY LOST ON THIS ONE! Apparently the
coffee maker isn't working right today, as I am just in-a-fog.....
HELP!
Andy

 

[Marshall Barton]

I have a report based on a query that shows training classes for a given date
range and lists all of the students that attended each class. This part works
fine. What I would like to do is add a list(subreport?) to the bottom of the
report that shows all of the students in the main report with a total number
of classes that each student attended. This way I can do a count of the
number of classes/the number of classes attended and see if each student is
meeting required criteria.

This sounds like a good use of a subreport. First create a
query that contain the desired data. THen use that as the
record source of a new report. Once you get this new report
working, add it as a subreport in the main report's report
footer section.

 

[serviceman via AccessMonster.com]

Hey Marshall,
Yeah, I think you are right, but I have NO CLUE as to how to get it started!
The main report groups on class_date and then class_id, and then the students
are listed in the detail section. how/where would this subreport go? I would
like the totals to be at the end of the report, but won't this subreport have
to go into the detail section in order to link to each student? I guess what
I am looking for is a running total of STUDENT_ID for each STUDENT_ID in the
report.
I-am-soooooooooooooooooooo-confused....
Andy

I have a report based on a query that shows training classes for a given date
range and lists all of the students that attended each class. This part works

[quoted text clipped - 3 lines]

number of classes/the number of classes attended and see if each student is
meeting required criteria.

This sounds like a good use of a subreport. First create a
query that contain the desired data. THen use that as the
record source of a new report. Once you get this new report
working, add it as a subreport in the main report's report
footer section.

 

[Marshall Barton]

Start by creating a query to gather/calculate the needed
data. If you want the summary subreport to display totals
by student, then use a Totals type query (View menu) with
Group By for the field(s) you want to see the total and then
use Count/Sum/Avg for the field(s) you want to calculate the
aggregate values. For example:

SELECT T.Student_ID, Count(*) As ClassesAttended
FROM yourtable As T
WHERE T.Class_Date Between [Start Date] And [End Date]
GROUP BY T.Stundent_ID

Once you get a query that produces the desired result, the
subreport should be very simple. Since you said you want
the summary at the end of the report, place the subreport in
the Report Footer section.
--
Marsh
MVP [MS Access]

Yeah, I think you are right, but I have NO CLUE as to how to get it started!
The main report groups on class_date and then class_id, and then the students
are listed in the detail section. how/where would this subreport go? I would
like the totals to be at the end of the report, but won't this subreport have
to go into the detail section in order to link to each student? I guess what
I am looking for is a running total of STUDENT_ID for each STUDENT_ID in the
report.

I have a report based on a query that shows training classes for a given date
range and lists all of the students that attended each class. This part works

[quoted text clipped - 3 lines]

number of classes/the number of classes attended and see if each student is
meeting required criteria.

This sounds like a good use of a subreport. First create a
query that contain the desired data. THen use that as the
record source of a new report. Once you get this new report
working, add it as a subreport in the main report's report
footer section.

 

[serviceman via AccessMonster.com]

Ok Marshall,
I got the query part; but how do I connect this to the main report? If the
main report is filtered by date, how do I connect this subreport if the date
field isn't actually in the query? If I connect this query as a subreport to
the main report using STUDENT_ID, then how do I pass the date filter
parameters to this subreport? I got to this point once before, but just could
not come up with a way to do it....
BTW...Good Morning!
Andy

Start by creating a query to gather/calculate the needed
data. If you want the summary subreport to display totals
by student, then use a Totals type query (View menu) with
Group By for the field(s) you want to see the total and then
use Count/Sum/Avg for the field(s) you want to calculate the
aggregate values. For example:

SELECT T.Student_ID, Count(*) As ClassesAttended
FROM yourtable As T
WHERE T.Class_Date Between [Start Date] And [End Date]
GROUP BY T.Stundent_ID

Once you get a query that produces the desired result, the
subreport should be very simple. Since you said you want
the summary at the end of the report, place the subreport in
the Report Footer section.

Yeah, I think you are right, but I have NO CLUE as to how to get it started!
The main report groups on class_date and then class_id, and then the students

[quoted text clipped - 15 lines]

 

[Marshall Barton]

You could add the date field to the query (be sure to
uncheck the Show box) and aooly the same criteria as the
main report's query.

In your case I think it would be better to base the
subreport's query on the main report query instead of going
directly to the table. This way you can be sure the two
reports are operating on the same set of records.
--
Marsh
MVP [MS Access]

I got the query part; but how do I connect this to the main report? If the
main report is filtered by date, how do I connect this subreport if the date
field isn't actually in the query? If I connect this query as a subreport to
the main report using STUDENT_ID, then how do I pass the date filter
parameters to this subreport?

Start by creating a query to gather/calculate the needed
data. If you want the summary subreport to display totals
by student, then use a Totals type query (View menu) with
Group By for the field(s) you want to see the total and then
use Count/Sum/Avg for the field(s) you want to calculate the
aggregate values. For example:

SELECT T.Student_ID, Count(*) As ClassesAttended
FROM yourtable As T
WHERE T.Class_Date Between [Start Date] And [End Date]
GROUP BY T.Stundent_ID

Once you get a query that produces the desired result, the
subreport should be very simple. Since you said you want
the summary at the end of the report, place the subreport in
the Report Footer section.

Yeah, I think you are right, but I have NO CLUE as to how to get it started!
The main report groups on class_date and then class_id, and then the students

[quoted text clipped - 15 lines]

working, add it as a subreport in the main report's report
footer section.

 

[serviceman via AccessMonster.com]

Ok Marshall,
I got it. I really do appreciate the help!
Andy

You could add the date field to the query (be sure to
uncheck the Show box) and aooly the same criteria as the
main report's query.

In your case I think it would be better to base the
subreport's query on the main report query instead of going
directly to the table. This way you can be sure the two
reports are operating on the same set of records.

I got the query part; but how do I connect this to the main report? If the
main report is filtered by date, how do I connect this subreport if the date

[quoted text clipped - 23 lines]