Fieldname not recognised - crosstab query error

[scubadiver007]

I am using the select query as one of the sources for a union query from which I calculate the payments.

SELECT PracticeCode, Field_ID, Quartercode, Val(Nz([numberofpatients]-(SELECT TOP 1 T.[numberofpatients] FROM Tble_CUB_activity AS T WHERE T.PracticeCode = Tble_CUB_activity.PracticeCode AND T.[FieldName] = Tble_CUB_activity.[fieldname] AND T.[yearnum] = Tble_CUB_activity.[yearnum] AND ((T.[quarternum]) - 1) < (([Tble_CUB_activity].[quarternum]) - 1) ORDER BY Val(((T.[quarternum]) - 1)) DESC),[numberofpatients])) AS activity
FROM Tble_CUB_activity
WHERE (((Tble_CUB_activity.Field_ID) Not Like "713*") AND ((Tble_CUB_activity.Numberofpatients)>0))
ORDER BY Tble_CUB_activity.PracticeCode, Tble_CUB_activity.Quartercode;

union query

SELECT practicecode, field_id, quartercode, activity
from qry_activity_CUB
UNION SELECT practicecode, fieldid, quartercode, qtrfigs
from qry_opiates_CUB;

I then use the union query to calculate payments:

SELECT Tble_Services.ID_Service, Tble_Services.Service, Tble_Practice.GMS_PMS, Qry_CUB.practicecode, Qry_CUB.field_id, Tble_Indicator.Query, Qry_CUB.quartercode, Qry_CUB.activity, [activity]*[unitcost] AS payment
FROM Tble_Services INNER JOIN (Tble_Practice INNER JOIN (Tble_Payment INNER JOIN (Tble_Indicator INNER JOIN Qry_CUB ON Tble_Indicator.ID_Indicator = Qry_CUB.field_id) ON (Tble_Payment.ID_Indicator = Qry_CUB.field_id) AND (Tble_Payment.Quarter = Qry_CUB.quartercode)) ON Tble_Practice.Practice_Code = Qry_CUB.practicecode) ON Tble_Services.ID_Service = Tble_Indicator.ID_Service;

Then...

SELECT Qry_payment_CUB.practicecode, Qry_payment_CUB.Query, Qry_payment_CUB.payment, Qry_payment_CUB.GMS_PMS, Qry_payment_CUB.quartercode
FROM Qry_payment_CUB
WHERE (((Qry_payment_CUB.payment)>0) AND ((Qry_payment_CUB.GMS_PMS)=Eval("[forms]![menu]![pay_contract]")) AND ((Qry_payment_CUB.quartercode)=Eval("[forms]![menu]![pay_qtr]")));

and finally

TRANSFORM Sum(Qry_Payment_src.payment) AS SumOfpayment
SELECT Qry_Payment_src.practicecode
FROM Qry_Payment_src
GROUP BY Qry_Payment_src.practicecode
PIVOT Qry_Payment_src.Query;

If I change the crosstab back to a select query it works fine but as a crosstab I get "fieldname not recognised" error. [Practicecode] is not a parameter so I am not sure why I am getting this error.

As far as I can see the error is in the subquery because the field name changes in the error.

 

[scubadiver007]

I have tried doing a crosstab on the first query and I still get the same error.

 

[vbaInet]

Does a normal SELECT query work?

 

[scubadiver007]

Does a normal SELECT query work?

Works fine so am a bit stumped.

 

[vbaInet]

Why do you have a field called query?

 

[scubadiver007]

It is a daft name which I have changed.

 

[vbaInet]

Can you show me a screenshot of your query in design view.

 

[scubadiver007]

The screenshot attached is for the query that has the problem and the error message for the crosstab.

 

[vbaInet]

It looks like the problem is coming from the first query. Have you tried recreating that query? Copy and paste the SQL statement into a new query.

If that doesn't work, upload a stripped down version of your db and I'll take a look.

 

[scubadiver007]

Database attached.

Table_CUB_activity -> qry_activity_CUB

Qry_opiates_CUB_src -> qry_opiates_CUB

Qry_activity_CUB and Qry_opiates_CUB -> qry_CUB

Qry_CUB -> Qry_payment_CUB -> Qry_payment_src -> Qry_payment_CT

Use the combo boxes on the form to select the criteria for Qry_payment_src.

I have created a test query as well based on qry_activity_CUB which produces the error.

 

[vbaInet]

scubadiver007,

Did you try what I suggested in my last post? I've just created a new query based on Qry_opiates_CUB_src, applied the criteria that you had in Qry_opiates_CUB and it works for me.

Also, in the future you will have difficulty debugging your queries because you are going too many levels deep. You need to re-think your approach.

 

[scubadiver007]

Hello,

That is not the query causing the problem. It is "Qry_activity_CUB".

As far as my query set up is concerned, I'm not sure how much choice I have.

The only set of data that does not have cumulative figures is the drug misuse so I cannot apply my subquery. In addition, payment is based on a three month average so this subset of data has to be kept separate.

If you can think of any ways I might be able to improve on what I have let me know.

Generally, to improve the performance, I have thought about appending the quarterly figures as fixed values instead of having to keep re-processing them for forms. It isn't good design but the values won't change.

 

[vbaInet]

Hello,

That is not the query causing the problem. It is "Qry_activity_CUB".

What do you mean? Opening that query doesn't display the error.

 

[scubadiver007]

What do you mean? Opening that query doesn't display the error.

Re-read the message with two attached jpegs. Run the "TEST query" cross-tab.

 

[vbaInet]

You didn't tell me you were using a subquery in one of your fields.

That kind of inline subquery is not allowed. You have two options:

1. Set Fixed Column Headings

OR

2. Replace the subquery with a DLookup() function.

 

[Taruz]

hi scubadiver007..

when examine the structure of the query, i do not think would be an alternative is other methods.. use of the temporary table is the best way i think in this case..

see the attachment..:

 

[vbaInet]

when examine the structure of the query, i do not think would be an alternative is other methods..

Are you saying that the two methods proposed will not work in this case?

 

[Taruz]

Are you saying that the two methods proposed will not work in this case?

I think so.. If you examine the subquery, you might think the same thing.. at least in my experience, I said it..

 

[vbaInet]

I think so.. If you examine the subquery, you might think the same thing.. at least in my experience, I said it..

Care to share why you think both methods won't work?

 

[scubadiver007]

You didn't tell me you were using a subquery in one of your fields.

That kind of inline subquery is not allowed. You have two options:

1. Set Fixed Column Headings

OR

2. Replace the subquery with a DLookup() function.

Using a Dlookup is absolutely out of the question!