Non-static data (1 Viewer)

[terrytek]

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.

 

[plog]

Please demonsrate your issue with data. Provide 2 sets of data:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you hope to end up with after you feed in the data from A.

Again, 2 sets of data.

 

[Ranman256]

Instead of using literal years,make a table of the Acedemic years,start-end:
2018, 8/15/2018, 6/1/2019
2017, 8/15/2017, 6/1/2018
Etc...

Then join this table to the test table to get AcYear for the results.

 

[The_Doc_Man]

The first question will be how you want to see this. You are in a design phase so you need to do some non-programming work first. I usually give people in this phase two rules to think about as philosophical mind-sets related to the work being done right now.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Your first duty is to decide what you want to do. Stepping into Access right now is like the USA Wild West practice of "Shooting from the hip in hopes of hitting the target." Not always good odds on that. You need to define on paper what it is that you hope to achieve. You need a road map. Using the map analogy, if you don't have a road map, how will you ever know that you have reached your destination? You surely won't have a blinking sign with an arrow that says "You are here!"

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first, or at least explain it.

When you want something, you have to be sure you have a way to get that something, because Access can't make something out of nothing. It needs data sources and formulas. Access is like a hand-held power tool. It only does one thing. It is up to you to apply it to your end goals. Once you have your road map, you will have an idea of what you want to see coming from this app. But here's the key: If you wanted XYZ out, you needed to have XYZ in, OR you needed X, Y, and Z in plus the conversion formula that combines them. That might mean working backwards from every desired output to determine the input that permits that output.

Frequently the problem is data organization. You need to think about how to view your data in the proper way to support the question you are about to ask. You need to ask questions about how much time can pass before two tests can no longer be counted as pre-test/post-test.

There have been posts here on how to compare two consecutive records against each other using SQL and using VBA. Look up the topic "Compare Adjacent Records" and see if anything helpful comes up.

 

[bastanu]

I thing you need to provide more info. What if there is a gap (medical leave) and the student has a tests on 1/1/16, 1/1/18, and 1/1/19. 1/1/18?
Is the first one still considered a pretest for the second or is there an "expiration date".
If you can provide a minimal example of your stripped down db with just a couple of records (no private info please) you would probably get a much more informed response. Right now I would say you would need to set a recordset to loop through the test table (sorted ascending by the test date) student by student and populate a local temp table (that gets emptied just before running the code) with your desired data: test qualifier ie pre vs post, test result(s), etc.

Than you build the report on that temp table.

Cheers,
Vlad

 

[terrytek]

The first question will be how you want to see this. You are in a design phase so you need to do some non-programming work first. I usually give people in this phase two rules to think about as philosophical mind-sets related to the work being done right now.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

Your first duty is to decide what you want to do. Stepping into Access right now is like the USA Wild West practice of "Shooting from the hip in hopes of hitting the target." Not always good odds on that. You need to define on paper what it is that you hope to achieve. You need a road map. Using the map analogy, if you don't have a road map, how will you ever know that you have reached your destination? You surely won't have a blinking sign with an arrow that says "You are here!"

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first, or at least explain it.

When you want something, you have to be sure you have a way to get that something, because Access can't make something out of nothing. It needs data sources and formulas. Access is like a hand-held power tool. It only does one thing. It is up to you to apply it to your end goals. Once you have your road map, you will have an idea of what you want to see coming from this app. But here's the key: If you wanted XYZ out, you needed to have XYZ in, OR you needed X, Y, and Z in plus the conversion formula that combines them. That might mean working backwards from every desired output to determine the input that permits that output.

Frequently the problem is data organization. You need to think about how to view your data in the proper way to support the question you are about to ask. You need to ask questions about how much time can pass before two tests can no longer be counted as pre-test/post-test.

There have been posts here on how to compare two consecutive records against each other using SQL and using VBA. Look up the topic "Compare Adjacent Records" and see if anything helpful comes up.

I already have queries that compare the last two tests that a student has had. This works fine for students in this year, but does not work for students who have had tests in previous years that can be considered post-tests (i.e. they have a test that was a post-test this year, which the database pulls as the Max Of Test Date, but they had a test last year that can also be considered a post-test for that year but is a pre-test for this year). I am trying to get an idea of how to work with this.

 

[terrytek]

I thing you need to provide more info. What if there is a gap (medical leave) and the student has a tests on 1/1/16, 1/1/18, and 1/1/19. 1/1/18?
Is the first one still considered a pretest for the second or is there an "expiration date".
If you can provide a minimal example of your stripped down db with just a couple of records (no private info please) you would probably get a much more informed response. Right now I would say you would need to set a recordset to loop through the test table (sorted ascending by the test date) student by student and populate a local temp table (that gets emptied just before running the code) with your desired data: test qualifier ie pre vs post, test result(s), etc.

Than you build the report on that temp table.

Cheers,
Vlad

Please see reply to plog for a demo db with the relevant data.

There is no expiration date, so the test sets would be
1/1/16 is pretest, 1/1/18 is its post
Then, 1/1/18 is pretest, 1/1/19 is its post

So I don’t really know how to handle how to tell the database to compare those tests appropriately. I have a query that compares the two most recent tests for a given student, but that doesn’t help for the 1/2016–1/2018 test set.

 

[terrytek]

Instead of using literal years,make a table of the Acedemic years,start-end:
2018, 8/15/2018, 6/1/2019
2017, 8/15/2017, 6/1/2018
Etc...

Then join this table to the test table to get AcYear for the results.

I already have this, but it doesn’t solve the issue, because when you have a test that is not a student’s most recent test, it can sometimes be both a pretest for the most recent test and a posstest for a previous test.

Labeling a test as pretest or posttest doesn’t work because if there’s three test dates, the date in the middle is both a pre and a posttest.

 

[terrytek]

Please demonsrate your issue with data. Provide 2 sets of data:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you hope to end up with after you feed in the data from A.

Again, 2 sets of data.

I have uploaded a demo database with the relevant info. I want the data to look like the report that is in the database (there is only one). The figures in that report now are only correct for the 2018-19 year, because if someone was posttested in both AY2019 and AY2018 (in other words, their AY2018 test was a posttest for 2018 but a pretest for 2019), they would not be counted in AY2018 because that test would not show up as their last test, and therefore, would not be counted in the 2018-19 figures.

 

[plog]

Nope, I want the data I described; not data from your failed attempt. 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases. You can give me a database with this data, but only give me the tables I need.

B. Expected results of A. This is going to require manual work on your part. Using the data you give me in A, show me the exact results you want. Don't give me a page of results and tell me only 1/4 of them are correct. I want full expected, correct results based on all the data in A.