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?
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?