Query breaks when adding new restriction

J

Jon

I have a problem with a query that I'm hoping the group can help me
solve. I have a report that is mildly complex which draws on the result
of three queries, each one feeding it's answers into the next one. It
all works fine except when I go to add the crowning item... it all
falls apart. Here's what I have, along with what i'm trying to get it
to do at each step:

The first query is called qryCatCalc. Here's the original SQL:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]));

What it's does is breaks the Footage value for each order down into
several different categories by footage (e.g., 0 to 4000 is category 0,
4000 to 5000 is cat 1, etc.). I run it and the result looks like this:

Category # purchase_id
2 300025
2 300034
5 300035

So far so good. The next query is qryOrderFrequency:

SELECT tblECTList.[ECT group], QryCatCalc.[Category #],
QryCatCalc.purchase_id
FROM QryCatCalc INNER JOIN (tblBoardCutToOrder INNER JOIN tblECTList ON
tblBoardCutToOrder.material_dsc = tblECTList.[Flute entry]) ON
QryCatCalc.purchase_id = tblBoardCutToOrder.purchase_id;

What's happening here is that it's now figuring out what ECT group each
purchase order belongs in based on the board. For instance, the values
200# C, 32A and 32C Oyster all belong to the same ECT group, which is
32. Once it derives that, it adds the previously gotten Category info.
The results look like this:

ECT group Category # purchase_id
32 5 306762
32 3 306763
32 7 306764


The last query is qryOrderFrequency_Crosstab:

TRANSFORM Count(qryOrderFrequency.purchase_id) AS CountOfpurchase_id
SELECT qryOrderFrequency.[ECT group],
Count(qryOrderFrequency.purchase_id) AS [Total Of purchase_id]
FROM qryOrderFrequency
WHERE (((qryOrderFrequency.[ECT group])<>"other"))
GROUP BY qryOrderFrequency.[ECT group]
PIVOT qryOrderFrequency.[Category #];

What this tells me is for each ECT group, how many orders fell into
each category. The results that come back are perfect. Now what I want
to do is to filter for a specific month for a specific year. So I
modified qryCatCalc to read as follow:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]) AND
((Month([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartMonth])
AND
((Year([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartYear]));

The first queries (qryCatCalc and qryOrderFrequency) both work fine,
bringing back only the results for that specific month. But when I run
the capping query (qryOrderFrequency_Crosstab), I get the following
error message:

The Microsoft Jet database engine does not recognize
'Forms!frmReportGeneration!txtStartMonth' as a valid field name or
expression.

Which completely baffles me as the two queries that feed into the final
query work perfectly. What's happening here and how can I get the query
to work?
 
J

Jeff Boyce

Jon

I wonder if you might need to explicitly declare the parameters in order for
the crosstab to work properly...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jon said:
I have a problem with a query that I'm hoping the group can help me
solve. I have a report that is mildly complex which draws on the result
of three queries, each one feeding it's answers into the next one. It
all works fine except when I go to add the crowning item... it all
falls apart. Here's what I have, along with what i'm trying to get it
to do at each step:

The first query is called qryCatCalc. Here's the original SQL:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]));

What it's does is breaks the Footage value for each order down into
several different categories by footage (e.g., 0 to 4000 is category 0,
4000 to 5000 is cat 1, etc.). I run it and the result looks like this:

Category # purchase_id
2 300025
2 300034
5 300035

So far so good. The next query is qryOrderFrequency:

SELECT tblECTList.[ECT group], QryCatCalc.[Category #],
QryCatCalc.purchase_id
FROM QryCatCalc INNER JOIN (tblBoardCutToOrder INNER JOIN tblECTList ON
tblBoardCutToOrder.material_dsc = tblECTList.[Flute entry]) ON
QryCatCalc.purchase_id = tblBoardCutToOrder.purchase_id;

What's happening here is that it's now figuring out what ECT group each
purchase order belongs in based on the board. For instance, the values
200# C, 32A and 32C Oyster all belong to the same ECT group, which is
32. Once it derives that, it adds the previously gotten Category info.
The results look like this:

ECT group Category # purchase_id
32 5 306762
32 3 306763
32 7 306764


The last query is qryOrderFrequency_Crosstab:

TRANSFORM Count(qryOrderFrequency.purchase_id) AS CountOfpurchase_id
SELECT qryOrderFrequency.[ECT group],
Count(qryOrderFrequency.purchase_id) AS [Total Of purchase_id]
FROM qryOrderFrequency
WHERE (((qryOrderFrequency.[ECT group])<>"other"))
GROUP BY qryOrderFrequency.[ECT group]
PIVOT qryOrderFrequency.[Category #];

What this tells me is for each ECT group, how many orders fell into
each category. The results that come back are perfect. Now what I want
to do is to filter for a specific month for a specific year. So I
modified qryCatCalc to read as follow:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]) AND
((Month([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartMonth])
AND
((Year([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartYear]));

The first queries (qryCatCalc and qryOrderFrequency) both work fine,
bringing back only the results for that specific month. But when I run
the capping query (qryOrderFrequency_Crosstab), I get the following
error message:

The Microsoft Jet database engine does not recognize
'Forms!frmReportGeneration!txtStartMonth' as a valid field name or
expression.

Which completely baffles me as the two queries that feed into the final
query work perfectly. What's happening here and how can I get the query
to work?
Click to expand...
 
C

Conan Kelly

Jeff,

Did you check out my post ("Crosstab query will work one way but not the other" posted today (6-23-2006) at 1:33 pm)?

I'm having almost the exact same problem, and I don't have any parameters.

I would really like to find an answer to this problem.

Thnak for all of your help,

Conan Kelly




Jeff Boyce said:
Jon

I wonder if you might need to explicitly declare the parameters in order for the crosstab to work properly...

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jon said:
I have a problem with a query that I'm hoping the group can help me
solve. I have a report that is mildly complex which draws on the result
of three queries, each one feeding it's answers into the next one. It
all works fine except when I go to add the crowning item... it all
falls apart. Here's what I have, along with what i'm trying to get it
to do at each step:

The first query is called qryCatCalc. Here's the original SQL:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]));

What it's does is breaks the Footage value for each order down into
several different categories by footage (e.g., 0 to 4000 is category 0,
4000 to 5000 is cat 1, etc.). I run it and the result looks like this:

Category # purchase_id
2 300025
2 300034
5 300035

So far so good. The next query is qryOrderFrequency:

SELECT tblECTList.[ECT group], QryCatCalc.[Category #],
QryCatCalc.purchase_id
FROM QryCatCalc INNER JOIN (tblBoardCutToOrder INNER JOIN tblECTList ON
tblBoardCutToOrder.material_dsc = tblECTList.[Flute entry]) ON
QryCatCalc.purchase_id = tblBoardCutToOrder.purchase_id;

What's happening here is that it's now figuring out what ECT group each
purchase order belongs in based on the board. For instance, the values
200# C, 32A and 32C Oyster all belong to the same ECT group, which is
32. Once it derives that, it adds the previously gotten Category info.
The results look like this:

ECT group Category # purchase_id
32 5 306762
32 3 306763
32 7 306764


The last query is qryOrderFrequency_Crosstab:

TRANSFORM Count(qryOrderFrequency.purchase_id) AS CountOfpurchase_id
SELECT qryOrderFrequency.[ECT group],
Count(qryOrderFrequency.purchase_id) AS [Total Of purchase_id]
FROM qryOrderFrequency
WHERE (((qryOrderFrequency.[ECT group])<>"other"))
GROUP BY qryOrderFrequency.[ECT group]
PIVOT qryOrderFrequency.[Category #];

What this tells me is for each ECT group, how many orders fell into
each category. The results that come back are perfect. Now what I want
to do is to filter for a specific month for a specific year. So I
modified qryCatCalc to read as follow:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]) AND
((Month([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartMonth])
AND
((Year([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartYear]));

The first queries (qryCatCalc and qryOrderFrequency) both work fine,
bringing back only the results for that specific month. But when I run
the capping query (qryOrderFrequency_Crosstab), I get the following
error message:

The Microsoft Jet database engine does not recognize
'Forms!frmReportGeneration!txtStartMonth' as a valid field name or
expression.

Which completely baffles me as the two queries that feed into the final
query work perfectly. What's happening here and how can I get the query
to work?
Click to expand...
Click to expand...
 
J

John Spencer

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Watch out, Access has a nasty habit of adding an extra set of [] around the
entire parameter. You may have to edit those out.

In SQL view, you would end up with something like the following.

Parameters [Forms]![frmReportGeneration]![txtStartMonth], DateTime
, [Forms]![frmReportGeneration]![txtStartYear], DateTime;
SELECT tblFootage.[Category #]
, tblPurchasesSince200311.purchase_id
FROM ...

Jon said:
I have a problem with a query that I'm hoping the group can help me
solve. I have a report that is mildly complex which draws on the result
of three queries, each one feeding it's answers into the next one. It
all works fine except when I go to add the crowning item... it all
falls apart. Here's what I have, along with what i'm trying to get it
to do at each step:

The first query is called qryCatCalc. Here's the original SQL:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]));

What it's does is breaks the Footage value for each order down into
several different categories by footage (e.g., 0 to 4000 is category 0,
4000 to 5000 is cat 1, etc.). I run it and the result looks like this:

Category # purchase_id
2 300025
2 300034
5 300035

So far so good. The next query is qryOrderFrequency:

SELECT tblECTList.[ECT group], QryCatCalc.[Category #],
QryCatCalc.purchase_id
FROM QryCatCalc INNER JOIN (tblBoardCutToOrder INNER JOIN tblECTList ON
tblBoardCutToOrder.material_dsc = tblECTList.[Flute entry]) ON
QryCatCalc.purchase_id = tblBoardCutToOrder.purchase_id;

What's happening here is that it's now figuring out what ECT group each
purchase order belongs in based on the board. For instance, the values
200# C, 32A and 32C Oyster all belong to the same ECT group, which is
32. Once it derives that, it adds the previously gotten Category info.
The results look like this:

ECT group Category # purchase_id
32 5 306762
32 3 306763
32 7 306764


The last query is qryOrderFrequency_Crosstab:

TRANSFORM Count(qryOrderFrequency.purchase_id) AS CountOfpurchase_id
SELECT qryOrderFrequency.[ECT group],
Count(qryOrderFrequency.purchase_id) AS [Total Of purchase_id]
FROM qryOrderFrequency
WHERE (((qryOrderFrequency.[ECT group])<>"other"))
GROUP BY qryOrderFrequency.[ECT group]
PIVOT qryOrderFrequency.[Category #];

What this tells me is for each ECT group, how many orders fell into
each category. The results that come back are perfect. Now what I want
to do is to filter for a specific month for a specific year. So I
modified qryCatCalc to read as follow:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]) AND
((Month([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartMonth])
AND
((Year([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartYear]));

The first queries (qryCatCalc and qryOrderFrequency) both work fine,
bringing back only the results for that specific month. But when I run
the capping query (qryOrderFrequency_Crosstab), I get the following
error message:

The Microsoft Jet database engine does not recognize
'Forms!frmReportGeneration!txtStartMonth' as a valid field name or
expression.

Which completely baffles me as the two queries that feed into the final
query work perfectly. What's happening here and how can I get the query
to work?
Click to expand...
 
J

Jon

That did it. I had the parameters for txtStartMonth and txtStartYear
set in the original query (qryCatCalc) but not for the crosstab. Once I
added parameters to the crosstab, query now seems to work. Thanks!

John said:
First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Watch out, Access has a nasty habit of adding an extra set of [] around the
entire parameter. You may have to edit those out.

In SQL view, you would end up with something like the following.

Parameters [Forms]![frmReportGeneration]![txtStartMonth], DateTime
, [Forms]![frmReportGeneration]![txtStartYear], DateTime;
SELECT tblFootage.[Category #]
, tblPurchasesSince200311.purchase_id
FROM ...

Jon said:
I have a problem with a query that I'm hoping the group can help me
solve. I have a report that is mildly complex which draws on the result
of three queries, each one feeding it's answers into the next one. It
all works fine except when I go to add the crowning item... it all
falls apart. Here's what I have, along with what i'm trying to get it
to do at each step:

The first query is called qryCatCalc. Here's the original SQL:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]));

What it's does is breaks the Footage value for each order down into
several different categories by footage (e.g., 0 to 4000 is category 0,
4000 to 5000 is cat 1, etc.). I run it and the result looks like this:

Category # purchase_id
2 300025
2 300034
5 300035

So far so good. The next query is qryOrderFrequency:

SELECT tblECTList.[ECT group], QryCatCalc.[Category #],
QryCatCalc.purchase_id
FROM QryCatCalc INNER JOIN (tblBoardCutToOrder INNER JOIN tblECTList ON
tblBoardCutToOrder.material_dsc = tblECTList.[Flute entry]) ON
QryCatCalc.purchase_id = tblBoardCutToOrder.purchase_id;

What's happening here is that it's now figuring out what ECT group each
purchase order belongs in based on the board. For instance, the values
200# C, 32A and 32C Oyster all belong to the same ECT group, which is
32. Once it derives that, it adds the previously gotten Category info.
The results look like this:

ECT group Category # purchase_id
32 5 306762
32 3 306763
32 7 306764


The last query is qryOrderFrequency_Crosstab:

TRANSFORM Count(qryOrderFrequency.purchase_id) AS CountOfpurchase_id
SELECT qryOrderFrequency.[ECT group],
Count(qryOrderFrequency.purchase_id) AS [Total Of purchase_id]
FROM qryOrderFrequency
WHERE (((qryOrderFrequency.[ECT group])<>"other"))
GROUP BY qryOrderFrequency.[ECT group]
PIVOT qryOrderFrequency.[Category #];

What this tells me is for each ECT group, how many orders fell into
each category. The results that come back are perfect. Now what I want
to do is to filter for a specific month for a specific year. So I
modified qryCatCalc to read as follow:

SELECT tblFootage.[Category #], tblPurchasesSince200311.purchase_id
FROM tblFootage, tblPurchasesSince200311
WHERE (((tblPurchasesSince200311.sqfpm) Between
[tblFootage].[FootageFrom] And [tblFootage].[FootageTo]) AND
((Month([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartMonth])
AND
((Year([tblPurchasesSince200311].[purchase_dte]))=[Forms]![frmReportGeneration]![txtStartYear]));

The first queries (qryCatCalc and qryOrderFrequency) both work fine,
bringing back only the results for that specific month. But when I run
the capping query (qryOrderFrequency_Crosstab), I get the following
error message:

The Microsoft Jet database engine does not recognize
'Forms!frmReportGeneration!txtStartMonth' as a valid field name or
expression.

Which completely baffles me as the two queries that feed into the final
query work perfectly. What's happening here and how can I get the query
to work?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need advice on speeding query up 8
Need help with query strategy 5

Top