Calculations based on counts in a crosstab

G

Guest

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.
 
G

Guest

Ben,

How do you handle students who transfer into a cohort and are not there
during the initial term?

Where you have your crosstab query, I'd use a Select query. It would look
something like (call this qry_Cohort_Term_Counts):

Select CohortNo, Term, Count(LastName) as Students
FROM yourQuery
GROUP BY CohortNo, Term

3. Then, you could create another query that uses two copies of
qry_Cohort_Term_Counts. You would link these on Cohort, and the query would
look something like:

Select Q1.CohortNo, Q1.Term, Q1.Students, Q2.Term as NextTerm, Q2.Students
as NextTermStudents, (Q2.Students/Q1.Students) as Pct
FROM qry_Cohort_Term_Counts Q1
LEFT JOIN qry_Cohort_Term_Counts Q2
ON Q1.CohortNo = Q2.CohortNo
AND Q2.Term = Q1.Term+1

Note: if you do the query this way, you will not be able to view it in
design view because of the inequality in the Term portion of the join. This
assumes that your terms are numbered, and not text. If they are text, I'd
probably build a table that ranks the terms and gives them a numeric sequence.

Then, If you want to do a crosstab to display the results, you can do that

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.


Ben Byers said:
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.
Click to expand...
 
G

Guest

Dale,

To your first question, I'm not aware of anyone being able to transfer into
a cohort. Cohort # is defined as the term they start. Folks would transfer
in later, they'd just be a part of the next cohort.

I'll give you suggestion a run through. One clarifying question. Are you
saying keep the intial count query, but as step 2 instead of a crosstab, do
your suggestion? Or just start with the select query?

Thanks,
Ben

Dale Fye said:
Ben,

How do you handle students who transfer into a cohort and are not there
during the initial term?

Where you have your crosstab query, I'd use a Select query. It would look
something like (call this qry_Cohort_Term_Counts):

Select CohortNo, Term, Count(LastName) as Students
FROM yourQuery
GROUP BY CohortNo, Term

3. Then, you could create another query that uses two copies of
qry_Cohort_Term_Counts. You would link these on Cohort, and the query would
look something like:

Select Q1.CohortNo, Q1.Term, Q1.Students, Q2.Term as NextTerm, Q2.Students
as NextTermStudents, (Q2.Students/Q1.Students) as Pct
FROM qry_Cohort_Term_Counts Q1
LEFT JOIN qry_Cohort_Term_Counts Q2
ON Q1.CohortNo = Q2.CohortNo
AND Q2.Term = Q1.Term+1

Note: if you do the query this way, you will not be able to view it in
design view because of the inequality in the Term portion of the join. This
assumes that your terms are numbered, and not text. If they are text, I'd
probably build a table that ranks the terms and gives them a numeric sequence.

Then, If you want to do a crosstab to display the results, you can do that

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.


Ben Byers said:
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.
Click to expand...
Click to expand...
 
D

Dale Fye

Ben,

Now that I take a closer look at your first query, I don't see a term in the
Select portion, so you will have to rewrite that query so that it includes
the Term, but I think you could use that as the basis of the second query.


Dale

Ben Byers said:
Dale,

To your first question, I'm not aware of anyone being able to transfer
into
a cohort. Cohort # is defined as the term they start. Folks would
transfer
in later, they'd just be a part of the next cohort.

I'll give you suggestion a run through. One clarifying question. Are you
saying keep the intial count query, but as step 2 instead of a crosstab,
do
your suggestion? Or just start with the select query?

Thanks,
Ben

Dale Fye said:
Ben,

How do you handle students who transfer into a cohort and are not there
during the initial term?

Where you have your crosstab query, I'd use a Select query. It would
look
something like (call this qry_Cohort_Term_Counts):

Select CohortNo, Term, Count(LastName) as Students
FROM yourQuery
GROUP BY CohortNo, Term

3. Then, you could create another query that uses two copies of
qry_Cohort_Term_Counts. You would link these on Cohort, and the query
would
look something like:

Select Q1.CohortNo, Q1.Term, Q1.Students, Q2.Term as NextTerm,
Q2.Students
as NextTermStudents, (Q2.Students/Q1.Students) as Pct
FROM qry_Cohort_Term_Counts Q1
LEFT JOIN qry_Cohort_Term_Counts Q2
ON Q1.CohortNo = Q2.CohortNo
AND Q2.Term = Q1.Term+1

Note: if you do the query this way, you will not be able to view it in
design view because of the inequality in the Term portion of the join.
This
assumes that your terms are numbered, and not text. If they are text,
I'd
probably build a table that ranks the terms and gives them a numeric
sequence.

Then, If you want to do a crosstab to display the results, you can do
that

HTH
Dale


--
Email address is not valid.
Please reply to newsgroup only.


Ben Byers said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Total based on status in crosstab 7
Crosstab queries as subforms error: "You can't use a pass-through. 3
Critera in Crosstab Query 2
Crosstab query has more problems 2
Calculating percentages from counts in a crosstab query 6
Get total number of records within a crosstab query 4
insert a crosstab in to temp table query 2
Crosstab Query - External Criteria 10

Top