problem with parameters and three related queries

[kar]

hi everybody, here is my problem, thanks in advance for reading.

I have three queries, second is based in the results of first, and third is based on second results.

the first one selects a bounch of records based in a field in the form ([forms]![Mainsplash].[Country]):

query 1 - SQL:

SELECT IDExp.FY, IDExp.FYM.Value, IDExp.FYC, IDExp.Type, dataExp.Cost_category, dataExp.Order_no, dataExp.Long_desc, dataExp.Group_amount, dataExp.Local_amount
FROM IDExp INNER JOIN dataExp ON IDExp.ID = dataExp.IDExp
WHERE (((IDExp.FYC)=[forms]![Mainsplash].[Country]) AND ((IDExp.Type)="ASPIRE"));

the second one is a crosstab and based on the results of first presents data with months in rows.

query 2 - SQL:


PARAMETERS [forms]![mainsplash].[country] Text ( 255 );
TRANSFORM Sum(CQS_Aspire2.Group_amount) AS SumOfGroup_amount
SELECT CQS_Aspire2.Cost_category, CQS_Aspire2.Long_desc, CQS_Aspire2.Order_no, Sum(CQS_Aspire2.Group_amount) AS [Total Of Group_amount]
FROM CQS_Aspire2
GROUP BY CQS_Aspire2.Cost_category, CQS_Aspire2.Long_desc, CQS_Aspire2.Order_no
PIVOT CQS_Aspire2.IDExp.FYM.Value;

Finally I did a third query in order to have the same results of the second one but with some calculated fields...

query 3 - SQL:


PARAMETERS [forms]![mainsplash].[country] Text ( 255 );
SELECT CQS_Aspire2_Crosstab.CQS_Aspire2.Cost_category, CQS_Aspire2_Crosstab.CQS_Aspire2.Long_desc, CQS_Aspire2_Crosstab.CQS_Aspire2.Order_no, CQS_Aspire2_Crosstab.November, CQS_Aspire2_Crosstab.December, CQS_Aspire2_Crosstab.January, [November]+[December]+[January] AS Expr1, CQS_Aspire2_Crosstab.February, CQS_Aspire2_Crosstab.March, CQS_Aspire2_Crosstab.April, [February]+[March]+[April] AS Expr2, CQS_Aspire2_Crosstab.May, CQS_Aspire2_Crosstab.June, CQS_Aspire2_Crosstab.July, [May]+[June]+[July] AS Expr3, CQS_Aspire2_Crosstab.August, CQS_Aspire2_Crosstab.September, CQS_Aspire2_Crosstab.October, [August]+[September]+[October] AS Expr4
FROM CQS_Aspire2_Crosstab;

And here is where is my problem, despite of the fact that first and second works fine, third one launches the error:

"The microsoft office access database engine does not recognize 'forms!mainsplash.country' as a valid field name or expression"

notice that I was having the same mistake for second query before I added "forms!mainsplash.country" as a parameter.

thank you very much.

 

[Guus2005]

In the second and third query there's a parameter initialized but never used. It is the country field in the first query. You don't need to use parameters to make it work. In fact you don't use parameters.

I have never used a crosstab query as a base for another query so i am not sure if that works. There are different ways to accomplish what you are trying to do.

HTH

 

[boblarson]

You do need the Parameters in the first and second query (for a crosstab it is important that they are in both).

And the third you should not need to use it. What happens if you remove it?

 

[kar]

Yes Thanks Guus and Bob, as you said bob using parameters in second and first queries solves the first problem I was dealing with, actually it was exactly the same error message:

if I dont use parameters in the first and second queries I get "The microsoft office access database engine does not recognize 'forms!mainsplash.country' as a valid field name or expression" when I try to execute the second one, so I added the parameters to the third trying to solve the same error message in the same way, but If I remove them, nothing happens, I just keep getting the same error for the third one.

I need to use the third query because I have a report that needs to show the calculated fields that I can not add in the crosstab.

thank you for your help.

 

[kar]

Some extra data:
If I do not use the parameters, I can perfectly use the third simple query based on crosstab second. but I have just noticed that when I use them I can't even use the wizard over the crosstab because when the wizard ask for the fields to select, there are not displayed, so I can select anything. it is like if there where no support to perform a simple query over a crosstab using parameters.

 

[vbaInet]

Some extra data:
If I do not use the parameters, I can perfectly use the third simple query based on crosstab second. but I have just noticed that when I use them I can't even use the wizard over the crosstab because when the wizard ask for the fields to select, there are not displayed, so I can select anything. it is like if there where no support to perform a simple query over a crosstab using parameters.

You cannot SELECT from columns that are dynamic. The columns in the crosstab must be static, so you need to change the PIVOT line of your Crosstab to this:

PIVOT Format(CQS_Aspire2.IDExp.FYM.Value,"mmmm") In ("January","February","March", [COLOR=Red][B]...[/B][/COLOR]);

... complete the rest of the months.

 

[kar]

vbaInet that worked just fine, problem solved, thanks.

 

[vbaInet]

Glad to hear!