[MS_AccessPros] crosstab query error: cannot use the crosstab of a non-fixed column as a subquery

 

I have the following 3 queries:

1) PRTABLE:

SELECT shift.[Shift Name], Patient.ptable, Patient.[patient id], Patient.[last name], Patient.[first name], diet.diet, diet_1.diet, diet_2.diet, diet_3.diet, pATIENT.DIETMISC, Patient.sun, Patient.mon, Patient.tue, Patient.wed, Patient.thu, Patient.fri, Patient.sat, Patient.[end date], Patient.[billing type], dietician.dietician
FROM (((((Patient INNER JOIN shift ON Patient.[preferred shift]=shift.[Shift #]) LEFT JOIN diet ON Patient.diet1cd=diet.dietcd) LEFT JOIN diet AS diet_1 ON Patient.diet2cd=diet_1.dietcd) LEFT JOIN diet AS diet_2 ON Patient.diet3cd=diet_2.dietcd) LEFT JOIN diet AS diet_3 ON Patient.diet4cd=diet_3.dietcd) LEFT JOIN dietician ON Patient.[dietician cd]=dietician.[dietician cd]
WHERE (((shift.[Shift Name]) Between  [Forms]![zform1 td]![sfrom] And [Forms]![zform1 td]![sto]) And ((Patient.[end date]) Is Null) And ((Patient.[billing type]) Like Forms![zform1 td]!pmb) And ((nz(patient![dietician cd],"")) Between nz(forms![zform1 td]![dt from],"") And nz(forms![zform1 td]![dt to],"")) And (([last name] & ", " & [first name] & " " & [mi]) Between Forms![zform1 td]![ln from] And Forms![zform1 td]![ln to]) And (forms![zform1 td]![pid from]<patient!frabsentdate Or Nz(patient!frabsentdate,"")="" Or forms![zform1 td]![pid from]>patient!toabsentdate Or Nz(patient!toabsentdate,"")=""))
ORDER BY shift.[Shift Name], IIf(forms![zform1 td]!option59=-1,Patient.ptable,dietician), Patient.[last name], patient.[first name], patient.[patient id], diet.diet;

2) temptableqry:

SELECT [patient id], 1 as Num, Diet.diet as Diet,ptable,[shift name] FROM PRtable WHERE PRtable.mon=True and diet.diet is not null UNION all SELECT [patient id], 2 , Diet_1.diet, ptable,[shift name]  FROM PRtable WHERE PRtable.mon=True and diet_1.diet is not null UNION all SELECT [patient id], 3 , Diet_2.diet,ptable,[shift name]  FROM PRtable WHERE PRtable.mon=True and diet_2.diet is not null UNION ALL SELECT [patient id], 4 , Diet_3.diet,ptable,[shift name]  FROM PRtable WHERE PRtable.mon=True and diet_3.diet is not null
ORDER BY [shift name], ptable, diet;

3) Query2:

TRANSFORM Count(temptableQry.[patient id]) AS [CountOfpatient id]
SELECT temptableQry.[shift name], temptableQry.ptable, temptableQry.Diet, Count(temptableQry.[patient id]) AS [Total Of patient id]
FROM temptableQry
GROUP BY temptableQry.[shift name], temptableQry.ptable, temptableQry.Diet
PIVOT temptableQry.cntm;

when I run query2 in vba, I get message:' cannot use the crosstab of a non-fixed column as a subquery' , when run as a query I get message:

The Microsoft Jet database engine does not recognize '[Forms]![zform 1 td]![sfrom]' as a valid field name or expression.

Please help

Thanks

Sarah

__._,_.___

---

Posted by: sarahk@schemesoftware.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___