Form input in query, Union and crosstab query gives error msg

[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

 

[Michel Walsh]

HI,


For a crosstab, you have to supply the datatype for each parameters. Use the
query designer, under the menu, Query, you have an item Parameters.

Only under some circumstances Jet+Access would solve, for you,
AUTOMATICALLY, the parameters of the syntax: FORMS!FormName!ControlName.
That won't occur if you use CurrentDb.OpenRecordset, as example.



Hoping it may help,
Vanderghast, Access MVP

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

 

[Christian]

Hi Michel,

I did do the trick, thank you!

I added the two parameters:
[Forms]![FrmSelectPlan]![RunSupplierCombo]
[Forms]![FrmSelectPlan]![RunPlanWeekCombo]
and defined them as text.

I would never ever have found the solution.
Thanks
- Chr