B
Ben Byers
My general goal: I have tblStudent with a an initial term (field = cohortno).
I have tblGatewayActivity which contains multiple term entries per student
in tblStudent. I want to calculate academic persistence per cohort, for each
term. Thus cohortno 1 starts with 20 students, after 1 term 18 students
remain, after 2 terms, 10 students remain. I am trying to cacluate
percentage remaining after each term.
I have an initial query (qryPersistenceEachTerm) to count the number of
tblGatewayActivity records per student. Query syntax: (preface, I didn't
build it, broken naming conventions not of my doing)
SELECT Count([tblGateway Activity].term) AS CountOfterm,
tblStudents.Lastname, tblApplicants.cohortno
FROM (tblStudents INNER JOIN (tblApplicants INNER JOIN [tblGateway Activity]
ON tblApplicants.ApplicationTableID=[tblGateway Activity].ApplicationTableID)
ON tblStudents.StudentTableID=tblApplicants.StudentTableID) INNER JOIN
tblGateway ON
(tblApplicants.ApplicationTableID=tblGateway.ApplicationTableID) AND
(tblGateway.ApplicationTableID=[tblGateway Activity].ApplicationTableID)
GROUP BY tblStudents.Lastname, tblApplicants.cohortno
ORDER BY Count([tblGateway Activity].term);
Next, I have a Crosstab query that sources this count query. Syntax:
TRANSFORM Count(qryPersistenceEachTerm.Lastname) AS CountOfLastname
SELECT qryPersistenceEachTerm.cohortno,
Count(qryPersistenceEachTerm.Lastname) AS [Total Of Lastname]
FROM qryPersistenceEachTerm
GROUP BY qryPersistenceEachTerm.cohortno
PIVOT qryPersistenceEachTerm.CountOfterm;
Everything is hunky-dorey up to this point, and the cross tab query returns
the appropariate values for me. The problem comes in trying to base
caculations off of the values in the crosstab. I have two versions of a
subsequent query. My initial one, without any NZ funtion calculates percent
with an Expression Expr1: ([Total Of Lastname]-[1])/[Total Of Lastname],
where [1] is a column head in the crosstab. This expression works correctly.
Since enrollment drops cumulatively, the expression for term 2 persistence
is: Expr2: ([Total Of Lastname]-([1]+[2]))/[Total Of Lastname], and so on.
The problem comes in that once [3] is null, all of the remaining results of
the expressions are returned as null, regardless of whether [4] or [5] is
null.
So I figure 0 instead of nulls. Via research I try NZ. Now I get values
beyond term 1 that are completely wrong. Same expression, but the values are
exponentially increasing negative numbers. -25.xxxx for term 2; -267.xxxx
for term 3; -2683.xxxx for term 4.
Help! I'm so close to my end goal. There is one more question, but if I
could get this, it be closer to solved.
I have tblGatewayActivity which contains multiple term entries per student
in tblStudent. I want to calculate academic persistence per cohort, for each
term. Thus cohortno 1 starts with 20 students, after 1 term 18 students
remain, after 2 terms, 10 students remain. I am trying to cacluate
percentage remaining after each term.
I have an initial query (qryPersistenceEachTerm) to count the number of
tblGatewayActivity records per student. Query syntax: (preface, I didn't
build it, broken naming conventions not of my doing)
SELECT Count([tblGateway Activity].term) AS CountOfterm,
tblStudents.Lastname, tblApplicants.cohortno
FROM (tblStudents INNER JOIN (tblApplicants INNER JOIN [tblGateway Activity]
ON tblApplicants.ApplicationTableID=[tblGateway Activity].ApplicationTableID)
ON tblStudents.StudentTableID=tblApplicants.StudentTableID) INNER JOIN
tblGateway ON
(tblApplicants.ApplicationTableID=tblGateway.ApplicationTableID) AND
(tblGateway.ApplicationTableID=[tblGateway Activity].ApplicationTableID)
GROUP BY tblStudents.Lastname, tblApplicants.cohortno
ORDER BY Count([tblGateway Activity].term);
Next, I have a Crosstab query that sources this count query. Syntax:
TRANSFORM Count(qryPersistenceEachTerm.Lastname) AS CountOfLastname
SELECT qryPersistenceEachTerm.cohortno,
Count(qryPersistenceEachTerm.Lastname) AS [Total Of Lastname]
FROM qryPersistenceEachTerm
GROUP BY qryPersistenceEachTerm.cohortno
PIVOT qryPersistenceEachTerm.CountOfterm;
Everything is hunky-dorey up to this point, and the cross tab query returns
the appropariate values for me. The problem comes in trying to base
caculations off of the values in the crosstab. I have two versions of a
subsequent query. My initial one, without any NZ funtion calculates percent
with an Expression Expr1: ([Total Of Lastname]-[1])/[Total Of Lastname],
where [1] is a column head in the crosstab. This expression works correctly.
Since enrollment drops cumulatively, the expression for term 2 persistence
is: Expr2: ([Total Of Lastname]-([1]+[2]))/[Total Of Lastname], and so on.
The problem comes in that once [3] is null, all of the remaining results of
the expressions are returned as null, regardless of whether [4] or [5] is
null.
So I figure 0 instead of nulls. Via research I try NZ. Now I get values
beyond term 1 that are completely wrong. Same expression, but the values are
exponentially increasing negative numbers. -25.xxxx for term 2; -267.xxxx
for term 3; -2683.xxxx for term 4.
Help! I'm so close to my end goal. There is one more question, but if I
could get this, it be closer to solved.