N
Norman Fritag
Hi there
for a program that could run from 1 to 10 years I record in a database the
number of students in a grade and how many students participated in that
program.
The group to traced is defined as kinder garden to grade 12 at the beginning
of the program.
I would have to generate reports, which are based on accumulative totals,
how many students participated in the program where they were at the
beginning of the program eg: 2003, how many student participated where the
students were today backwards to the beginning of the program and from any
other point in time as required.
eg:
1) base on 2003 reporting produced in 2004:
the report will include kindergarten to grade 12 in 2003, 1 to grade 11
in 2004 (exclude Kindergarten in 2004)
2) base on current reporting produced in 2004:
the report will include 1 to grade 11 in 2003, kindergarten to grade 12
in 2004 (exclude Kindergarten in 2003)
3) Accumulative total report produced today: include kindergarten to 12 for
both years
The issue on the reporting side is that students move from one grade to the
next grade over time, some of then may not, but that is excluded from the
assumption. We record only the assumptive progressive total.
Over time we don't record already reported students again, only those who
have not been recorded.
Students, who where at the beginning of the program in grade 7 are assumed
(plus / minus) in grade 8 in 2004, in grade 9 in 2005, in grade 10 in 2006,
in grade 11 in 2007, in grade 12 in 2008 out of the reporting period in
2009.
The Number of students in the grade is used as a reference to validate that
the number of students participated in the program don't exceed the number
of students in the grade. If it does we record sequentially and over time
what the excess is and increase the number of students in the grade at the
begin of the year. eg: start of the year: Stud in grade 150, in 2004 excess
20, studingrade in 2003 = 170 and though forth.
The Grades are as time passes recorded as grade when the visit occurred in
the relevant year, not as grade where they would have been at the beginning
of the program.
eg: How a Students record in 2003 would look like for the same school and
same grades
Grade 7 8 9 10 11 12
StudinGrade 100, 150, 250, 200, 300, 250
StudParitcipated 75, 55, 150, 125, 225, 189
How a Students record in 2004 would look like for the same school and same
grades
Grade 7 8 9 10 11 12
StudParitcipated 100,15 ,100, 125, 125, 125
How a Students record of 2004 would look like for the same school and same
grades
when reported on as of the beginning of the project
2003 data:
Grade 7 8 9 10 11 12
StudinGrade 100, 150, 250, 200, 300, 250
StudParitcipated 75, 55, 150, 125, 225, 189
2004 data:
Grade 6 7 8 9 10 11
StudParitcipated 100, 15 , 100, 125, 125, 125
I require a 3rd dimension that would allow me to rollup (report) on the
number of students in grades base on the beginning of the project as well
where we are currently (diagonal and liniar reporting).
2003| Grad 7 + 8 + 9 + 10 --year linear ----------->
\ \ /
2004| Grad 7 + 8 + 9 +10
\ X
2005| Grad 7 + 8 + 9 +10
/ \ \
2006| Grad 7 + 8 + 9 +10
/ \
2007| Grad 7 + 8 + 9 +10+11
\> = 2003 base reporting
the table structure looks like followed:
RecordingID = autonumber (PK)
Schoolid = Number (fk to schoolstable)
Area = text (5) (fk to Area in Country)
Datevisted = date
WeekNumber = number (weeknumber of the school visit)
Year = number ( year of school visit)
Initialviste = yes /no ( was this a first visit or a follow-up?)
Grades2003 = number (grade - (currentyear - starting year) eg 2004 - 2003)
Garde = number ( number of student in the grade)
Gradescurrent = number (grade + (currentyear - starting year) eg 2004 -
2003)
StudinGrade = number ( number of studentsin the grade)
SequnetialIncrease = number (record of number of students exceeding
studingrade)
StudParitcipated = number ( number of students participated)
DateEntered =date
any input hints or else is very much appreciated
Kind Regards
Norman
for a program that could run from 1 to 10 years I record in a database the
number of students in a grade and how many students participated in that
program.
The group to traced is defined as kinder garden to grade 12 at the beginning
of the program.
I would have to generate reports, which are based on accumulative totals,
how many students participated in the program where they were at the
beginning of the program eg: 2003, how many student participated where the
students were today backwards to the beginning of the program and from any
other point in time as required.
eg:
1) base on 2003 reporting produced in 2004:
the report will include kindergarten to grade 12 in 2003, 1 to grade 11
in 2004 (exclude Kindergarten in 2004)
2) base on current reporting produced in 2004:
the report will include 1 to grade 11 in 2003, kindergarten to grade 12
in 2004 (exclude Kindergarten in 2003)
3) Accumulative total report produced today: include kindergarten to 12 for
both years
The issue on the reporting side is that students move from one grade to the
next grade over time, some of then may not, but that is excluded from the
assumption. We record only the assumptive progressive total.
Over time we don't record already reported students again, only those who
have not been recorded.
Students, who where at the beginning of the program in grade 7 are assumed
(plus / minus) in grade 8 in 2004, in grade 9 in 2005, in grade 10 in 2006,
in grade 11 in 2007, in grade 12 in 2008 out of the reporting period in
2009.
The Number of students in the grade is used as a reference to validate that
the number of students participated in the program don't exceed the number
of students in the grade. If it does we record sequentially and over time
what the excess is and increase the number of students in the grade at the
begin of the year. eg: start of the year: Stud in grade 150, in 2004 excess
20, studingrade in 2003 = 170 and though forth.
The Grades are as time passes recorded as grade when the visit occurred in
the relevant year, not as grade where they would have been at the beginning
of the program.
eg: How a Students record in 2003 would look like for the same school and
same grades
Grade 7 8 9 10 11 12
StudinGrade 100, 150, 250, 200, 300, 250
StudParitcipated 75, 55, 150, 125, 225, 189
How a Students record in 2004 would look like for the same school and same
grades
Grade 7 8 9 10 11 12
StudParitcipated 100,15 ,100, 125, 125, 125
How a Students record of 2004 would look like for the same school and same
grades
when reported on as of the beginning of the project
2003 data:
Grade 7 8 9 10 11 12
StudinGrade 100, 150, 250, 200, 300, 250
StudParitcipated 75, 55, 150, 125, 225, 189
2004 data:
Grade 6 7 8 9 10 11
StudParitcipated 100, 15 , 100, 125, 125, 125
I require a 3rd dimension that would allow me to rollup (report) on the
number of students in grades base on the beginning of the project as well
where we are currently (diagonal and liniar reporting).
2003| Grad 7 + 8 + 9 + 10 --year linear ----------->
\ \ /
2004| Grad 7 + 8 + 9 +10
\ X
2005| Grad 7 + 8 + 9 +10
/ \ \
2006| Grad 7 + 8 + 9 +10
/ \
2007| Grad 7 + 8 + 9 +10+11
\> = 2003 base reporting
the table structure looks like followed:
RecordingID = autonumber (PK)
Schoolid = Number (fk to schoolstable)
Area = text (5) (fk to Area in Country)
Datevisted = date
WeekNumber = number (weeknumber of the school visit)
Year = number ( year of school visit)
Initialviste = yes /no ( was this a first visit or a follow-up?)
Grades2003 = number (grade - (currentyear - starting year) eg 2004 - 2003)
Garde = number ( number of student in the grade)
Gradescurrent = number (grade + (currentyear - starting year) eg 2004 -
2003)
StudinGrade = number ( number of studentsin the grade)
SequnetialIncrease = number (record of number of students exceeding
studingrade)
StudParitcipated = number ( number of students participated)
DateEntered =date
any input hints or else is very much appreciated
Kind Regards
Norman