My database stores information about an educational program that runs on the academic year. I need to show how many students had posttesting, i.e., they had at least two tests to compare. I need to have the posttesting data for each academic year on a report. The database goes back to the 2015-16 academic year.
The problem I am wrestling with is archival data. For the current academic year, my query that finds the two most recent tests for a given student is fine. The most recent test is the posttest, the penultimate is the pretest. However, for academic years before this current one, how can I pull out that data? A test can be both a pretest and a posttest, ex.
Student has tests on 1/1/17, 1/1/18, and 1/1/19. 1/1/18 is the posttest for 1/1/17 but the pretest for 1/1/19. So the student had posttests in both 2018 and 2019.
Would there be a way to do this using VBA? Or should I make a snapshot subreport for each academic year and put a the subreports on a report? Or is there some other way to do this?
Thank you as always.
The problem I am wrestling with is archival data. For the current academic year, my query that finds the two most recent tests for a given student is fine. The most recent test is the posttest, the penultimate is the pretest. However, for academic years before this current one, how can I pull out that data? A test can be both a pretest and a posttest, ex.
Student has tests on 1/1/17, 1/1/18, and 1/1/19. 1/1/18 is the posttest for 1/1/17 but the pretest for 1/1/19. So the student had posttests in both 2018 and 2019.
Would there be a way to do this using VBA? Or should I make a snapshot subreport for each academic year and put a the subreports on a report? Or is there some other way to do this?
Thank you as always.