ACCESS FEED: Re: [MS_AccessPros] help with query:cannot use the crosstab of a non-fixed column as a subquery

Selasa, 06 Agustus 2019

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

Hi Sarah,

I would put the parameter data types in the query where they are called. They are only needed in one.

I assume you don't know or the cntm field values are not static.

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of sarahk@schemesoftware.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, August 6, 2019 4:07 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] help with query:cannot use the crosstab of a non-fixed column as a subquery

getting syntax errors on:

PARAMETERS [forms]![zform1 td]![sfrom] DateTime, [Forms]![zform1 td]![sto] DateTime

still trying this. not sure which query it belongs in: 1, 2, 3 or all 3.

don't understand:

If you specify every possible value in the cntm field in the Column Headings property you might not have to specify all the parameter data types.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

In the query design, there is an icon in the ribbon "Parameters" that you click. This is an example however it uses a parameter prompt rather than Forms!frmName!ControlName.

https://www.techrepublic.com/article/specify-data-types-in-access-parameter-queries/

You would need to enter each fully qualified control name on a line in the parameter type dialog.

[Forms]![zform1 td]![sfrom] Date with Time

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, August 6, 2019 12:53 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: Re: [MS_AccessPros] help with query:cannot use the crosstab of a non-fixed column as a subquery

Excuse my ignorance , but how do I declare a data type in the query?

Thanks

Sarah

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Hi Sarah,

Any time you create a crosstab that has a reference to a control on a form you must declare the data type of the control/field in the Query Parameters property of the crosstab or possibly in the source queries. You have about 10 parameters that need to have their data type specified.

If you specify every possible value in the cntm field in the Column Headings property you might not have to specify all the parameter data types.

I would consider performing the union query in the first query without the Diet table and then join to the Diet table in the second query.

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of sarahk@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, August 5, 2019 6:23 PM
To: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] help with query:cannot use the crosstab of a non-fixed column as a subquery

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;

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;

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