It isn't so much a "formula" that you need but either a query, or a relationship to match to the correct data using inequality operators.
Example, the match fields in your relationship might be something like this:
Reports::Date >= Enrollment::EnrollmentDate AND
Reports::Date <= Enrollment::DischargeDate AND
Reports::StudentName = Enrollment::Name
And it's unfortunate that the only student identifier is a name. Names are not unique and even student names may be changed and this can create issues for your reporting.
I'm not quite sure I understand how this works. Can you elaborate on this.
I've also been rethinking this.
Am I correct that you have a single table where each record lists a student a single time with the fields you listed?
And you want to list each month of the current Year and show every month where the student was enrolled on the first day of that month as "enrolled"?
I have 2 tables, though I may be pursuing this wrong and you may throw out my concept and completely re do it.
I only need for the end result to be what I described above. How I get there doesn't matter.
That said here is what i have set up.
1 which is the student profile with the following fields
- enrollment date
- discharge date
- student name
The second is a table that represents the year with the following fields
- each month of the year (so 12 fields)
I was approaching this so each month field in table two would have a calculation to evaluate the enrollment date and discharge date. The results would be
- if student A was enrolled that month the month field for that student would return "active"
- if student B was not enrolled until the following month, it would return a blank field
- if the student discharged during the month the it would return active until the end of the month.
- the month after a student's discharge would return "inactive"
I hope this helps clarify. thanks for your help
I have some test calucalations that seem to be working to some extent if you want to review some of those.
Or like I said you can toss out all i've done so far and approach it from another angle.
How I get there doesn't matter.
Actually, it does matter. If you get there in a way that makes working with or modifying your system hard to do, it isn't a good approach to use.
What you are describing is called a "cross tab" report. A search here will bring up multiple different methods for how you can set them up. None of them are particularly simple to set up. So in addition to any assistance that you get here, you might search this forum using those keywords to find and compare the various methods that you might use.
But first, another question: Do you want to select or find a specific student and see their enrollment over a given year or list of years? Or do you want to see this for one year, but for multiple students?
For simplicity's sake, I would create a single table, STUDENTS, with these 16 fields. I think it would do what you want:
1. NAME (Text)
2. DATE_ENROLL (Date)
3. DATE_DISCHARGE (Date)
4. YEAR (Number, Global)
5. JAN (Calculation, Text) = If ( Date ( 2 ; 1 ; YEAR ) - 1 >= DATE_ENROLL ; If ( Date ( 1 ; 1 ; YEAR ) > DATE_DISCHARGE ; "Inactive" ; "Active" ) )
15. NOV (Calculation, Text) = If ( Date ( 12 ; 1 ; YEAR ) - 1 >= DATE_ENROLL ; If ( Date ( 11 ; 1 ; YEAR ) > DATE_DISCHARGE ; "Inactive" ; "Active" ) )
16. DEC (Calculation, Text) = If ( Date ( 1 ; 1 ; YEAR + 1 ) - 1 >= DATE_ENROLL ; If ( Date ( 12 ; 1 ; YEAR ) > DATE_DISCHARGE ; "Inactive" ; "Active" ) )