C
Christian
Hi NG,
I'm trying to create a crosstab query, but it fails. The content used in the
crosstab is a bit tricky to get and involves several steps.
The 3 steps and queries are listed below.
I use a form to get the user input to supplier and planweek. Each week there
is 15000 records, and by using these two input the number of records is
reduced to less than 1000 in step 1 below.
The query in step 3 works as a normal query, but when I select crosstab
query I get error message ala:
"..Jet... Does not recognize...
=[Forms]![FrmSelectPlan]![RunPlanWeekCombo] as a valid field name or
expression"
Is what I'm trying to do not possible in Access? Should I approach this in a
different way?
Any comment or suggestions are warmly welcome.
Here's the details
1st Step: Listing relevant records from tables based on user input via Form
QrySysa:
SELECT TblSysa.PlanWeek, TblSysa.ForecastWeek, TblSysa.Site,
TblSysaParts.Supplier, Sum(TblSysa.Volume) AS SysaVolume
FROM TblSysaParts LEFT JOIN TblSysa ON TblSysaParts.Part = TblSysa.Part
GROUP BY TblSysa.PlanWeek, TblSysa.ForecastWeek, TblSysa.Site,
TblSysaParts.Supplier
HAVING (((TblSysa.PlanWeek)=[Forms]![FrmSelectPlan]![RunPlanWeekCombo])
AND ((TblSysaParts.Supplier)=[Forms]![FrmSelectPlan]![RunSupplierCombo]))
ORDER BY TblSysa.PlanWeek, TblSysa.ForecastWeek, TblSysa.Site;
QrySysb:
SELECT TblSysb.PlanWeek, TblSysb.Site, TblSysb.ForecastWeek, TblSysb.Volume
AS SysbVolume
FROM TblSysb
WHERE (((TblSysb.PlanWeek)=[Forms]![FrmSelectPlan]![RunPlanWeekCombo]) AND
((TblSysb.ItemCategory)="BTR"));
2d Step: Using UNION ALL to join data from the two queries QrySysa & QrySysb
QryUnion:
Select Supplier, Site, PlanWeek, ForecastWeek, SysaVolume as Volume, "Sysa"
as Source
from QrySysa
UNION ALL Select Null, Site, PlanWeek, ForecastWeek, SysbVolume as Volume,
"Sysb" as Source
from QrySysb;
3rd Step: Adding additional attributes to QryUnion from another table and
creating crosstab
QryReportCross:
TRANSFORM Sum(QryUnion.Volume) AS SumOfVolume
SELECT TblSites.Site, TblSites.Region, TblSites.IntExt, TblSites.Company,
TblSites.Group, TblSites.GroupSort,
QryUnion.Supplier, QryUnion.Source
FROM TblSites INNER JOIN QryUnion ON TblSites.Site = QryUnion.Site
GROUP BY TblSites.Site, TblSites.Region, TblSites.IntExt, TblSites.Company,
TblSites.Group, TblSites.GroupSort,
QryUnion.Supplier, QryUnion.Source
PIVOT QryUnion.ForecastWeek;
Result:
QryReportCross: "..Jet Does not recognize..
=[Forms]![FrmSelectPlan]![RunPlanWeekCombo] as a valid field name or
expression"
Br
- Chr
I'm trying to create a crosstab query, but it fails. The content used in the
crosstab is a bit tricky to get and involves several steps.
The 3 steps and queries are listed below.
I use a form to get the user input to supplier and planweek. Each week there
is 15000 records, and by using these two input the number of records is
reduced to less than 1000 in step 1 below.
The query in step 3 works as a normal query, but when I select crosstab
query I get error message ala:
"..Jet... Does not recognize...
=[Forms]![FrmSelectPlan]![RunPlanWeekCombo] as a valid field name or
expression"
Is what I'm trying to do not possible in Access? Should I approach this in a
different way?
Any comment or suggestions are warmly welcome.
Here's the details
1st Step: Listing relevant records from tables based on user input via Form
QrySysa:
SELECT TblSysa.PlanWeek, TblSysa.ForecastWeek, TblSysa.Site,
TblSysaParts.Supplier, Sum(TblSysa.Volume) AS SysaVolume
FROM TblSysaParts LEFT JOIN TblSysa ON TblSysaParts.Part = TblSysa.Part
GROUP BY TblSysa.PlanWeek, TblSysa.ForecastWeek, TblSysa.Site,
TblSysaParts.Supplier
HAVING (((TblSysa.PlanWeek)=[Forms]![FrmSelectPlan]![RunPlanWeekCombo])
AND ((TblSysaParts.Supplier)=[Forms]![FrmSelectPlan]![RunSupplierCombo]))
ORDER BY TblSysa.PlanWeek, TblSysa.ForecastWeek, TblSysa.Site;
QrySysb:
SELECT TblSysb.PlanWeek, TblSysb.Site, TblSysb.ForecastWeek, TblSysb.Volume
AS SysbVolume
FROM TblSysb
WHERE (((TblSysb.PlanWeek)=[Forms]![FrmSelectPlan]![RunPlanWeekCombo]) AND
((TblSysb.ItemCategory)="BTR"));
2d Step: Using UNION ALL to join data from the two queries QrySysa & QrySysb
QryUnion:
Select Supplier, Site, PlanWeek, ForecastWeek, SysaVolume as Volume, "Sysa"
as Source
from QrySysa
UNION ALL Select Null, Site, PlanWeek, ForecastWeek, SysbVolume as Volume,
"Sysb" as Source
from QrySysb;
3rd Step: Adding additional attributes to QryUnion from another table and
creating crosstab
QryReportCross:
TRANSFORM Sum(QryUnion.Volume) AS SumOfVolume
SELECT TblSites.Site, TblSites.Region, TblSites.IntExt, TblSites.Company,
TblSites.Group, TblSites.GroupSort,
QryUnion.Supplier, QryUnion.Source
FROM TblSites INNER JOIN QryUnion ON TblSites.Site = QryUnion.Site
GROUP BY TblSites.Site, TblSites.Region, TblSites.IntExt, TblSites.Company,
TblSites.Group, TblSites.GroupSort,
QryUnion.Supplier, QryUnion.Source
PIVOT QryUnion.ForecastWeek;
Result:
QryReportCross: "..Jet Does not recognize..
=[Forms]![FrmSelectPlan]![RunPlanWeekCombo] as a valid field name or
expression"
Br
- Chr