If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Report Based on Subform Data
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 |
#3
|
|||
|
|||
Report Based on Subform Data
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 |
#4
|
|||
|
|||
Report Based on Subform Data
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 |
#5
|
|||
|
|||
Report Based on Subform Data
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 |
#6
|
|||
|
|||
Report Based on Subform Data
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. |
#7
|
|||
|
|||
Report Based on Subform Data
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reporting subreport total on main report | BobV | Setting Up & Running Reports | 22 | November 1st, 2005 03:19 AM |
How do I save an access document in word document? | cmartin | General Discussion | 2 | September 13th, 2005 11:26 PM |
Has anyone seen this behaviour? What might it be? | tw | General Discussion | 4 | June 30th, 2005 03:23 PM |
Data on report based on current date | JenniferM | Setting Up & Running Reports | 4 | April 18th, 2005 10:45 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |