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

Duane,

Thank you for all your help. "I" actually got this to work.

All the best (for now),

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

Hi Sarah,

I expect you can use the Link Master/Child of the subreport control to filter the records in the subreport to a value in the main report.

Regards,

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: Wednesday, August 7, 2019 10:07 AM
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

Thanks , that did it.

Follow up question:

This query is the data source for a subreport of the main report. The main report is sorted by table and list all the participants and their diets. The subreport is a summary by table of totals by diet.

Can I put something like this into the 'Where' statement: [ptable]=[Parent].[Report].[ptable].

Sarah

Sorry, for the duplicate postings, I could not see if my reply was posted.

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

Hi Sarah,

There should be a semi-colon between the last parameter data type and "TRANSFORM". This is added automatically when you enter your parameters in the dialog box provided.

Regards,

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 6:35 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

SQL:
PARAMETERS [forms]![zform1 td]![sfrom] DateTime, [Forms]![zform1 td]![sto] DateTime
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;

Error:
Syntax error in parameter clause

Sarah

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

Hi Sarah,

Please provide the SQL view of a query that errors as well as the error message.

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 4:04 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

I am working on this, still getting syntax errors on :

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

Not sure which query it belongs in, tried all 3 but still getting error.

don't understand what you mean by:

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