Report Based on Subform Data

I have a form that includes a subform. The main form uses a collection of
data from the subform to create some calculations and formulations. The form
and subform work great. I made a report using the main form as the sourse
because it makes calculations from the subform that a query couldn't. Like
it takes the last date listed for a student in the subform, and the last
class a student took listed in the subform and listed that last date and last
class in the report. My problem is, once students have passed a certain
class, I don't want them listed in the report any longer. So once they have
passed class 7, I want to filter them from the report results. I still want
their overall progress listed in the form.... just filtered from this
particular report. Since the report is based on form data, I don't know how
to do this. In the past, I've only filtered data using an underlying query
or table. I tried to make a query based on the subform table, but couldn't
get it to find the last date and last class taken for each student.

Can anyone tell me how to filter data from a report that is based on data
from a form?

Thanks!

Kass

Kass,

Best way to think about this, I think: Forms don't have data. Forms
display data, but the ultimate repository of the data is in the tables.
"The report is based on form data" is not really on target. If you
can see data on a form, then it is either data in a table, or derived
from data in a table. Look at your tables, and queries based on your
tables, or calculated controls using data from your tables, to provide
what you want for your report. I can't really be more specific without
more details of what you are doing, but that's the principle.

--
Steve Schapel, Microsoft Access MVP

Kass wrote:
I have a form that includes a subform. The main form uses a collection of
data from the subform to create some calculations and formulations. The form
and subform work great. I made a report using the main form as the sourse
because it makes calculations from the subform that a query couldn't. Like
it takes the last date listed for a student in the subform, and the last
class a student took listed in the subform and listed that last date and last
class in the report. My problem is, once students have passed a certain
class, I don't want them listed in the report any longer. So once they have
passed class 7, I want to filter them from the report results. I still want
their overall progress listed in the form.... just filtered from this
particular report. Since the report is based on form data, I don't know how
to do this. In the past, I've only filtered data using an underlying query
or table. I tried to make a query based on the subform table, but couldn't
get it to find the last date and last class taken for each student.

Can anyone tell me how to filter data from a report that is based on data
from a form?

Thanks!

Kass

Steve,

Thanks for getting back with me and for the clarity. I'm not always the
best at describing things.... so if you'll bear with me, I'll try again.

In my database is a table, Participants containing personal and registration
data on our recreation program participants. From that I have a query,
Participants Swim Query, that takes only the personal and registration data
needed for swimming lessons students. The main form mentioned in the earlier
post, Swim Lesson Records Form, utilizes or displays data fom the
Participants Swim Query (like PID, LastName, FirstName, Waiver?, SwimLesson?,
HomePhone, Birthdate, and Age (which is calculated: =(Date()-[Birthdate])/365
..... I know there are more sophisicated and precise formulas for arriving at
age, but I wanted a 1 decimal birth number to tell us at a glance how close
to an age a kid is).

To continue, on the Swim Lessons Records Form is a subform, Swim Lesson
Detail subform, that shows in datasheet view, comprehensive swim lesson class
records for a particular student for all the classes they have taken at our
facility. We use this to keep track of their progress and to be sure the
parent gets them enrolled in the correct course next time they sign up for
lessons. Here is the record source for the subform:

SELECT [Swim Lesson Detail Table].SLDID, [Swim Lesson Detail
Table].ParticipantID, [Swim Lesson Detail Table].LastName, [Swim Lesson
Detail Table].FirstName, [Swim Lesson Detail Table].Level, [Swim Lesson
Detail Table].[Date Completed], [Swim Lesson Detail Table].Instuctor, [Swim
Lesson Detail Table].PFI, [Swim Lesson Detail Table].Comments, [Swim Lesson
Detail Table].[Next Level], [Swim Lesson Detail Table].[Last Date Taken],
[Swim Lesson Detail Table].[Register Level] FROM [Swim Lesson Detail Table]
ORDER BY [Swim Lesson Detail Table].SLDID DESC;

Then at the bottom of the Swim Lesson Records Form main form I have the
following text boxes to calculate comprehensive data needed for the report.

Next Level... control source is: =[Swim Lesson Detail Table
subform].Form![Next Level] This is originally calculated in the subform Next
Level by this formula: =IIf([PFI]="Pass",[Level]+1,[Level])

Last Date Taken... control source is: =[Swim Lesson Detail Table
subform].Form![Date Completed]

Last Instructor... control source is: =[Swim Lesson Detail Table
subform].Form!Instuctor

Register Level... control source is: =[Next Level]

I sorted the subform by SLDID Descending so the last or most recent record
entry for the student will be listed at the top of the subform in datasheet
view and therefore will be displayed in the above mentioned text box
calculations.

The report I mentioned and need help with takes the above information to
tell us a student's last and first name; their DOB and age; what swim lesson
level they were last enrolled in; the date of that course; who their
Instructor was; and what level they should be enrolled in the next time they
sign up for lessons.

Now, what I'm having trouble with is: We have 6 Levels of swim lessons. I
don't want to list kids who have passed out of the system... in otherwords,
after they have successfully completed Level 6, I don't want to display them
in this report any longer. How do I get my report that is based on a Select
Query from data listed and calculated in a form and its subform to filter
out all records that have a Register Level of 7 or greater.

I'm sorry if I gave too much detail. I hope you can help. Thanks for your
time!

Kass



"Steve Schapel" wrote:

Kass,

Best way to think about this, I think: Forms don't have data. Forms
display data, but the ultimate repository of the data is in the tables.
"The report is based on form data" is not really on target. If you
can see data on a form, then it is either data in a table, or derived
from data in a table. Look at your tables, and queries based on your
tables, or calculated controls using data from your tables, to provide
what you want for your report. I can't really be more specific without
more details of what you are doing, but that's the principle.

--
Steve Schapel, Microsoft Access MVP

Kass wrote:
I have a form that includes a subform. The main form uses a collection of
data from the subform to create some calculations and formulations. The form
and subform work great. I made a report using the main form as the sourse
because it makes calculations from the subform that a query couldn't. Like
it takes the last date listed for a student in the subform, and the last
class a student took listed in the subform and listed that last date and last
class in the report. My problem is, once students have passed a certain
class, I don't want them listed in the report any longer. So once they have
passed class 7, I want to filter them from the report results. I still want
their overall progress listed in the form.... just filtered from this
particular report. Since the report is based on form data, I don't know how
to do this. In the past, I've only filtered data using an underlying query
or table. I tried to make a query based on the subform table, but couldn't
get it to find the last date and last class taken for each student.

Can anyone tell me how to filter data from a report that is based on data
from a form?

Thanks!

Kass

Kass,

Thanks for the further information. However, I think I already had a
grasp of the general concept. So sorry, it is me who has not been good
at explaining. Here's what I mean:

*Don't* do it like that. *Forget* the form exists. Get the Record
Source for your report from *table* data.

Make a query that gives you the most recent class for the student.
Apply your criteria ([Level]6 Or ([Level]=6 And [PFI]"Pass")) I
suppose. In another query, join this query to the Participants table,
and base your report on that.

Please let me know if I am missing something here.

--
Steve Schapel, Microsoft Access MVP

Kass wrote:
Steve,

Thanks for getting back with me and for the clarity. I'm not always the
best at describing things.... so if you'll bear with me, I'll try again.

In my database is a table, Participants containing personal and registration
data on our recreation program participants. From that I have a query,
Participants Swim Query, that takes only the personal and registration data
needed for swimming lessons students. The main form mentioned in the earlier
post, Swim Lesson Records Form, utilizes or displays data fom the
Participants Swim Query (like PID, LastName, FirstName, Waiver?, SwimLesson?,
HomePhone, Birthdate, and Age (which is calculated: =(Date()-[Birthdate])/365
.... I know there are more sophisicated and precise formulas for arriving at
age, but I wanted a 1 decimal birth number to tell us at a glance how close
to an age a kid is).

To continue, on the Swim Lessons Records Form is a subform, Swim Lesson
Detail subform, that shows in datasheet view, comprehensive swim lesson class
records for a particular student for all the classes they have taken at our
facility. We use this to keep track of their progress and to be sure the
parent gets them enrolled in the correct course next time they sign up for
lessons. Here is the record source for the subform:

SELECT [Swim Lesson Detail Table].SLDID, [Swim Lesson Detail
Table].ParticipantID, [Swim Lesson Detail Table].LastName, [Swim Lesson
Detail Table].FirstName, [Swim Lesson Detail Table].Level, [Swim Lesson
Detail Table].[Date Completed], [Swim Lesson Detail Table].Instuctor, [Swim
Lesson Detail Table].PFI, [Swim Lesson Detail Table].Comments, [Swim Lesson
Detail Table].[Next Level], [Swim Lesson Detail Table].[Last Date Taken],
[Swim Lesson Detail Table].[Register Level] FROM [Swim Lesson Detail Table]
ORDER BY [Swim Lesson Detail Table].SLDID DESC;

Then at the bottom of the Swim Lesson Records Form main form I have the
following text boxes to calculate comprehensive data needed for the report.

Next Level... control source is: =[Swim Lesson Detail Table
subform].Form![Next Level] This is originally calculated in the subform Next
Level by this formula: =IIf([PFI]="Pass",[Level]+1,[Level])

Last Date Taken... control source is: =[Swim Lesson Detail Table
subform].Form![Date Completed]

Last Instructor... control source is: =[Swim Lesson Detail Table
subform].Form!Instuctor

Register Level... control source is: =[Next Level]

I sorted the subform by SLDID Descending so the last or most recent record
entry for the student will be listed at the top of the subform in datasheet
view and therefore will be displayed in the above mentioned text box
calculations.

The report I mentioned and need help with takes the above information to
tell us a student's last and first name; their DOB and age; what swim lesson
level they were last enrolled in; the date of that course; who their
Instructor was; and what level they should be enrolled in the next time they
sign up for lessons.

Now, what I'm having trouble with is: We have 6 Levels of swim lessons. I
don't want to list kids who have passed out of the system... in otherwords,
after they have successfully completed Level 6, I don't want to display them
in this report any longer. How do I get my report that is based on a Select
Query from data listed and calculated in a form and its subform to filter
out all records that have a Register Level of 7 or greater.

I'm sorry if I gave too much detail. I hope you can help. Thanks for your
time!

Kass

Steve,

I appreciate your patience. Normally, when I do forms and reports, I try
joining tables and/or query combinations to get the data combinations that I
need to carry out certain tasks and then, as you suggest, use the query to
perform the formula calculations, etc. needed to make the database work for
me. This time, however, I ran into a problem. When I tried to use the
personal information (Name, Phone, Birthdate, etc) from the Participants
Table and the Swim Lesson Detail information (Level, Course Date, Instructor,
PFI for Pass, Fail, Incomplete, Next Level, etc..) from either the Swim
Lesson Detail Table or a Swim Lesson Detail Query, I ended up with the
multiple records for each student showing up in the query. That is why I
used the form the way I did... I got all the results for the report just as
needed, except the issue of weeding out students who have completed the last
course.

Perhaps I need to be more adept at VB script or something to make this work
the way I need it to. I think I know a way around this, unless you have a
better idea.... but I don't want to waste more of your time, that is for
sure. I may just put a checkbox in the system that would allow me to check
students who have completed the system and I can weed them out that way. It
might be easier that fighting this.

Kass

"Steve Schapel" wrote:

Kass,

Thanks for the further information. However, I think I already had a
grasp of the general concept. So sorry, it is me who has not been good
at explaining. Here's what I mean:

*Don't* do it like that. *Forget* the form exists. Get the Record
Source for your report from *table* data.

Make a query that gives you the most recent class for the student.
Apply your criteria ([Level]6 Or ([Level]=6 And [PFI]"Pass")) I
suppose. In another query, join this query to the Participants table,
and base your report on that.

Please let me know if I am missing something here.

--
Steve Schapel, Microsoft Access MVP

Kass wrote:
Steve,

Thanks for getting back with me and for the clarity. I'm not always the
best at describing things.... so if you'll bear with me, I'll try again.

In my database is a table, Participants containing personal and registration
data on our recreation program participants. From that I have a query,
Participants Swim Query, that takes only the personal and registration data
needed for swimming lessons students. The main form mentioned in the earlier
post, Swim Lesson Records Form, utilizes or displays data fom the
Participants Swim Query (like PID, LastName, FirstName, Waiver?, SwimLesson?,
HomePhone, Birthdate, and Age (which is calculated: =(Date()-[Birthdate])/365
.... I know there are more sophisicated and precise formulas for arriving at
age, but I wanted a 1 decimal birth number to tell us at a glance how close
to an age a kid is).

To continue, on the Swim Lessons Records Form is a subform, Swim Lesson
Detail subform, that shows in datasheet view, comprehensive swim lesson class
records for a particular student for all the classes they have taken at our
facility. We use this to keep track of their progress and to be sure the
parent gets them enrolled in the correct course next time they sign up for
lessons. Here is the record source for the subform:

SELECT [Swim Lesson Detail Table].SLDID, [Swim Lesson Detail
Table].ParticipantID, [Swim Lesson Detail Table].LastName, [Swim Lesson
Detail Table].FirstName, [Swim Lesson Detail Table].Level, [Swim Lesson
Detail Table].[Date Completed], [Swim Lesson Detail Table].Instuctor, [Swim
Lesson Detail Table].PFI, [Swim Lesson Detail Table].Comments, [Swim Lesson
Detail Table].[Next Level], [Swim Lesson Detail Table].[Last Date Taken],
[Swim Lesson Detail Table].[Register Level] FROM [Swim Lesson Detail Table]
ORDER BY [Swim Lesson Detail Table].SLDID DESC;

Then at the bottom of the Swim Lesson Records Form main form I have the
following text boxes to calculate comprehensive data needed for the report.

Next Level... control source is: =[Swim Lesson Detail Table
subform].Form![Next Level] This is originally calculated in the subform Next
Level by this formula: =IIf([PFI]="Pass",[Level]+1,[Level])

Last Date Taken... control source is: =[Swim Lesson Detail Table
subform].Form![Date Completed]

Last Instructor... control source is: =[Swim Lesson Detail Table
subform].Form!Instuctor

Register Level... control source is: =[Next Level]

I sorted the subform by SLDID Descending so the last or most recent record
entry for the student will be listed at the top of the subform in datasheet
view and therefore will be displayed in the above mentioned text box
calculations.

The report I mentioned and need help with takes the above information to
tell us a student's last and first name; their DOB and age; what swim lesson
level they were last enrolled in; the date of that course; who their
Instructor was; and what level they should be enrolled in the next time they
sign up for lessons.

Now, what I'm having trouble with is: We have 6 Levels of swim lessons. I
don't want to list kids who have passed out of the system... in otherwords,
after they have successfully completed Level 6, I don't want to display them
in this report any longer. How do I get my report that is based on a Select
Query from data listed and calculated in a form and its subform to filter
out all records that have a Register Level of 7 or greater.

I'm sorry if I gave too much detail. I hope you can help. Thanks for your
time!

Kass

Kass,

Well, it's up to you. The checkbox idea would work. I can't comment on
the report the way you've done it, as I don't know how you've gone about
getting the desired data in there, so I don't know why you can't apply
the level 6 / pass criteria to that.

But I presume the reason you ended up with multiple records for each
student when you tried in a query was because you were using all records
in the [Swim Lesson Detail Table] table? Whereas my suggestion was to
start by "make a query that gives you the most recent class for the
student", and then build from that. There are several ways this can be
done. Probably the easiest to follow is doing it in 2 steps. First
make a query that gives the most recent class data for each student.
Something like this...
SELECT ParticipantID, Max([SLDID]) As LatestLesson
FROM [Swim Lesson Detail Table]
GROUP BY ParticipantID

Save that query, then make another query which includes the [Swim Lesson
Detail Table] table and this first query, joined on both fields, and
also the Participants table, joined on the ParticipantID field. Put in
your "level 6 / pass" criteria. Assuming I understand you correctly,
you should be able to get everything you want for your report from this
query. Have a go, it's pretty easy.

--
Steve Schapel, Microsoft Access MVP


Kass wrote:
Steve,

I appreciate your patience. Normally, when I do forms and reports, I try
joining tables and/or query combinations to get the data combinations that I
need to carry out certain tasks and then, as you suggest, use the query to
perform the formula calculations, etc. needed to make the database work for
me. This time, however, I ran into a problem. When I tried to use the
personal information (Name, Phone, Birthdate, etc) from the Participants
Table and the Swim Lesson Detail information (Level, Course Date, Instructor,
PFI for Pass, Fail, Incomplete, Next Level, etc..) from either the Swim
Lesson Detail Table or a Swim Lesson Detail Query, I ended up with the
multiple records for each student showing up in the query. That is why I
used the form the way I did... I got all the results for the report just as
needed, except the issue of weeding out students who have completed the last
course.

Perhaps I need to be more adept at VB script or something to make this work
the way I need it to. I think I know a way around this, unless you have a
better idea.... but I don't want to waste more of your time, that is for
sure. I may just put a checkbox in the system that would allow me to check
students who have completed the system and I can weed them out that way. It
might be easier that fighting this.

Steve,

I've been gone and haven't had a chance to check this out yet, but I think I
understand what you are proposing here. I'll give it a go and if I have any
more questions, I'll be back. Thanks again for your time and patience!

"Steve Schapel" wrote:

Kass,

Well, it's up to you. The checkbox idea would work. I can't comment on
the report the way you've done it, as I don't know how you've gone about
getting the desired data in there, so I don't know why you can't apply
the level 6 / pass criteria to that.

But I presume the reason you ended up with multiple records for each
student when you tried in a query was because you were using all records
in the [Swim Lesson Detail Table] table? Whereas my suggestion was to
start by "make a query that gives you the most recent class for the
student", and then build from that. There are several ways this can be
done. Probably the easiest to follow is doing it in 2 steps. First
make a query that gives the most recent class data for each student.
Something like this...
SELECT ParticipantID, Max([SLDID]) As LatestLesson
FROM [Swim Lesson Detail Table]
GROUP BY ParticipantID

Save that query, then make another query which includes the [Swim Lesson
Detail Table] table and this first query, joined on both fields, and
also the Participants table, joined on the ParticipantID field. Put in
your "level 6 / pass" criteria. Assuming I understand you correctly,
you should be able to get everything you want for your report from this
query. Have a go, it's pretty easy.

--
Steve Schapel, Microsoft Access MVP


Kass wrote:
Steve,

I appreciate your patience. Normally, when I do forms and reports, I try
joining tables and/or query combinations to get the data combinations that I
need to carry out certain tasks and then, as you suggest, use the query to
perform the formula calculations, etc. needed to make the database work for
me. This time, however, I ran into a problem. When I tried to use the
personal information (Name, Phone, Birthdate, etc) from the Participants
Table and the Swim Lesson Detail information (Level, Course Date, Instructor,
PFI for Pass, Fail, Incomplete, Next Level, etc..) from either the Swim
Lesson Detail Table or a Swim Lesson Detail Query, I ended up with the
multiple records for each student showing up in the query. That is why I
used the form the way I did... I got all the results for the report just as
needed, except the issue of weeding out students who have completed the last
course.

Perhaps I need to be more adept at VB script or something to make this work
the way I need it to. I think I know a way around this, unless you have a
better idea.... but I don't want to waste more of your time, that is for
sure. I may just put a checkbox in the system that would allow me to check
students who have completed the system and I can weed them out that way. It
might be easier that fighting this.