I have the following 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;
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;
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;
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;
query2 gives message in vba: cannot use the crosstab of a non-fixed column as a subquery
or 'The Microsoft Jet database engine does not recognize '[forms]![zform1 td]![sfrom]' as a valid field name or expression'
What am I doing wrong ?
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) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar