This question is a long two-parter.
(1) I have query with student testing information. I would like to make a new query to show only those students who have taken a post-test. The query I have now is
qryTwoLastTests
I have attached an annotated photo of the query results, which are not exactly what I want (the annotations elaborate). The only way I could figure out only the students who have a post-test was to look for students who have more than one test session, so their Student ID is listed more than once. Problem is, some students take more than one test on a given date (like Reading and Math). I only want the students returned in the query that have two tests of the same [TestTypeID] on two different dates. If necessary, I could add a field where the choices are pretest and posttest, but I am afraid that the end users would not use the field properly (once someone takes a posttest, that posttest now becomes the pretest for the same test type on a later date).
(2) If I could ever get the query in (1) to return the desired results, I could move on to the following query, which is based on query (1) and calculates the difference in [SSNum] and [GENum] for a student's two most recent test dates of the same test type:
(a) This query either runs VERY slowly or crashes Access 2007. Is that due to all of the calculated fields and/or the fact that the query on which it's based returns results that make some of the calculations null (because the student took more than one test on the same day but neither of them have posttests yet, like student 146 in the photo)? Is there a way to rewrite this query, or break it up, or do what I'm trying to do in VBA instead (I know almost no VBA)? I'm not experienced enough to know.
(b) As I've mentioned earlier, I need this query to also be able to compare two SETS of tests on two different dates, as with student 156 in photo. The DMax in this query can pull the most recent test date, but what if the student has the same most recent test date for a Reading and a Math test? I need to compare both sets of tests.
Thank you for reading my long question, and thank you to this forum for all your help; I've learned a lot already, but have so far to go.....
(1) I have query with student testing information. I would like to make a new query to show only those students who have taken a post-test. The query I have now is
qryTwoLastTests
Code:
SELECT a.StudentID, a.TestDate, a.TestTypeID, a.TestType, a.SSNum, a.GENum, a.NextForm
FROM qryTwoLastTests AS a INNER JOIN qryTwoLastTests AS b ON a.StudentID = b.StudentID
WHERE (((a.TestTypeID)<>[b].[TestTypeID]) OR ((a.TestDate)<>[b].[TestDate]))
GROUP BY a.StudentID, a.TestDate, a.TestTypeID, a.TestType, a.SSNum, a.GENum, a.NextForm;
(2) If I could ever get the query in (1) to return the desired results, I could move on to the following query, which is based on query (1) and calculates the difference in [SSNum] and [GENum] for a student's two most recent test dates of the same test type:
Code:
SELECT qryTwoLastTests.StudentID, DMax("TestDate","qryTwoLastTests","StudentID=" & [StudentID]) AS LastTestDate, DLookUp("TestTypeID","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate = #" & [LastTestDate] & "#") AS LastTT, DLookUp("SSNum","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate = #" & [LastTestDate] & "#") AS LastSS, DLookUp("GENum","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate = #" & [LastTestDate] & "#") AS LastGE, DMax("TestDate","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate < #" & [LastTestDate] & "#") AS PrevTestDate, IIf(IsNull([PrevTestDate]),Null,DLookUp("TestType","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate= #" & [PrevTestDate] & "#")) AS PrevTT, IIf(IsNull([PrevTestDate]),Null,DLookUp("SSNum","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate= #" & [PrevTestDate] & "#")) AS PrevSS, IIf(IsNull([PrevTestDate]),Null,DLookUp("GENum","qryTwoLastTests","StudentID=" & [StudentID] & " and TestDate= #" & [PrevTestDate] & "#")) AS PrevGE, ([LastSS]-[PrevSS]) AS SSDiff, ([LastGE]-[PrevGE]) AS GEDiff
FROM qryTwoLastTests
GROUP BY qryTwoLastTests.StudentID
HAVING (((Count(qryTwoLastTests.StudentID))>=2))
ORDER BY qryTwoLastTests.StudentID;
(b) As I've mentioned earlier, I need this query to also be able to compare two SETS of tests on two different dates, as with student 156 in photo. The DMax in this query can pull the most recent test date, but what if the student has the same most recent test date for a Reading and a Math test? I need to compare both sets of tests.
Thank you for reading my long question, and thank you to this forum for all your help; I've learned a lot already, but have so far to go.....