Crosstab queries as subforms error: "You can't use a pass-through.

[Guest]

I have a crosstab that shows how many times a student missed class per term,
where missing a class can be one of three types: "Late, Miss, Threw out".
Since each miss has a mandatory date assigned to it, I count the date to get
the totals:

TERM Late Miss Threw Out Total Times Missed
1 2 1 3
2 1 1 2
3 1 1


The code I use to generate this is:

PARAMETERS[Forms]![frmStudent]![cboStudent] Long;
TRANSFORM Count(qryMiss_Pivot.Date) AS CountOfDate
SELECT qryMiss_Pivot.TERM AS TERM, Count(qryMiss_Pivot.Date) AS [Total Times
Missed]
FROM qryMiss_Pivot
WHERE (((qryMiss_Pivot.STUDENT_ID)=[Forms]![frmStudent]![cboAluno]))
GROUP BY qryMiss_Pivot.TERM
PIVOT qryMiss_Pivot.tblMiss.Type;

This info should be displayed in the a student's form that has already
relevant data, so I use a sub form to display the crosstab info but I keep on
getting the error "You can't use a pass-through query or an non-fixed-column
crosstab query as a record source for a sub form or sub report". Remember
that I want the crosstab to be filtered and calculated per student

Also, will the crosstab get the value for the parameter automatically or do
I have to feed it in an event?

Thanks.

 

[Duane Hookom]

Add Column Headings to your query:
PARAMETERS[Forms]![frmStudent]![cboStudent] Long;
TRANSFORM Count(qryMiss_Pivot.Date) AS CountOfDate
SELECT qryMiss_Pivot.TERM AS TERM, Count(qryMiss_Pivot.Date) AS [Total Times
Missed]
FROM qryMiss_Pivot
WHERE (((qryMiss_Pivot.STUDENT_ID)=[Forms]![frmStudent]![cboAluno]))
GROUP BY qryMiss_Pivot.TERM
PIVOT qryMiss_Pivot.tblMiss.Type IN ("Late","Miss","Threw Out");

 

[Guest]

Thank you, it works.
If it's not too much trouble, could you explain why adding the IN clause
makes it work?

Thanks,
PV

"Duane Hookom" escreveu:

 

[Duane Hookom]

A crosstab query generally needs to run in order to determine the columns
that get returned. By adding values to the Column Headings property, the
query doesn't have to be run in order to determine the columns.

--
Duane Hookom
MS Access MVP
--

Thank you, it works.
If it's not too much trouble, could you explain why adding the IN clause
makes it work?

Thanks,
PV

"Duane Hookom" escreveu:

Add Column Headings to your query:
PARAMETERS[Forms]![frmStudent]![cboStudent] Long;
TRANSFORM Count(qryMiss_Pivot.Date) AS CountOfDate
SELECT qryMiss_Pivot.TERM AS TERM, Count(qryMiss_Pivot.Date) AS [Total
Times
Missed]
FROM qryMiss_Pivot
WHERE (((qryMiss_Pivot.STUDENT_ID)=[Forms]![frmStudent]![cboAluno]))
GROUP BY qryMiss_Pivot.TERM
PIVOT qryMiss_Pivot.tblMiss.Type IN ("Late","Miss","Threw Out");