Dinamically Build a CrossTab

J

John

I have a form with the following selections a user may select:
1. Date format: "Ordinal" or "Calendar"
2. Group By: "JV ID","CMIS ID", or "Craft"
3. Value to show: "PF", "Actual Cost", "Earned Cost", "Quantity", "Actual
Hours", "Earned Hours" (this list could grow)

What I would like to do is create one cross tab query that creates the query
based on the values the user selects. Given the available selections, this
could be a total of 30 different crosstab queries. I don't want to create 30
different queries and then do a long VBA "Selelect" statement to run the
appropreate query. Is there an easy way to handle this? I have tried the
following and I get an error "The Microsoft Jet database engin does not
recongize 'forms!frmGntOpt!CmbDteFmt' as a valid field name or expresson."
Here's the code:

======

TRANSFORM
IIf(forms!frmGntOpt!cmbValues="PF",tblMonthly!pf_td,IIf(forms!frmGntOpt!cmbValues="Quantity",tblMonthly!quan_td,IIf(forms!frmGntOpt!cmbValues="Earned
Cost",tblMonthly!ebudgt_td,IIf(forms!frmGntOpt!cmbValues="Actual
Cost",tblMonthly!actualttd,IIf(forms!frmGntOpt!cmbValues="Earned
Hours",tblMonthly!whourerntd,tblMonthly!whouracttd))))) AS [Values]
SELECT IIf(forms!frmGntOpt!cmbGrpBy="CMIS
ID",tblActMap!Act,IIf(forms!frmGntOpt!cmbGrpBy="JV
ID",tblActMap!JVID,tblActMap!Srt3)) AS Description
FROM tblJVTitles RIGHT JOIN (tblCostOwners RIGHT JOIN (tblCMIS_Type RIGHT
JOIN (tblCMIS_Craft RIGHT JOIN (tblCMIS_Cat RIGHT JOIN ((tblMonthly LEFT JOIN
tblActMap ON tblMonthly.act = tblActMap.Act) LEFT JOIN tblProjInfo ON
tblMonthly.period = tblProjInfo.CMIS_Mo) ON tblCMIS_Cat.Str1 =
tblActMap.Srt1) ON tblCMIS_Craft.Srt3 = tblActMap.Srt3) ON tblCMIS_Type.Srt2
= tblActMap.Srt2) ON tblCostOwners.CO_Ini = tblActMap.CstOwnIni) ON
tblJVTitles.JVID = tblActMap.JVID
GROUP BY IIf(forms!frmGntOpt!cmbGrpBy="CMIS
ID",tblActMap!Act,IIf(forms!frmGntOpt!cmbGrpBy="JV
ID",tblActMap!JVID,tblActMap!Srt3))
ORDER BY IIf(forms!frmGntOpt!cmbGrpBy="CMIS
ID",tblActMap!Act,IIf(forms!frmGntOpt!cmbGrpBy="JV
ID",tblActMap!JVID,tblActMap!Srt3))
PIVOT
IIf(forms!frmGntOpt!CmbDteFmt="Calendar",tblProjInfo!Period_Dte,tblProjInfo!Period);

======

Thanks in advance!
 
K

KARL DEWEY

Parameters for crosstab queries must be defined in the crosstab query even if
the parameter is used in a feeder query.
Open the query in design view and click on menu Query - Parameters and enter
parameter name and datatype. Copy and paste things like
forms!frmGntOpt!CmbDteFmt to make sure it is exactly the same.
 
J

John

Karl,

That was the catch! Thanks for the help!!!
--
Thanks - John


KARL DEWEY said:
Parameters for crosstab queries must be defined in the crosstab query even if
the parameter is used in a feeder query.
Open the query in design view and click on menu Query - Parameters and enter
parameter name and datatype. Copy and paste things like
forms!frmGntOpt!CmbDteFmt to make sure it is exactly the same.
--
KARL DEWEY
Build a little - Test a little


John said:
I have a form with the following selections a user may select:
1. Date format: "Ordinal" or "Calendar"
2. Group By: "JV ID","CMIS ID", or "Craft"
3. Value to show: "PF", "Actual Cost", "Earned Cost", "Quantity", "Actual
Hours", "Earned Hours" (this list could grow)

What I would like to do is create one cross tab query that creates the query
based on the values the user selects. Given the available selections, this
could be a total of 30 different crosstab queries. I don't want to create 30
different queries and then do a long VBA "Selelect" statement to run the
appropreate query. Is there an easy way to handle this? I have tried the
following and I get an error "The Microsoft Jet database engin does not
recongize 'forms!frmGntOpt!CmbDteFmt' as a valid field name or expresson."
Here's the code:

======

TRANSFORM
IIf(forms!frmGntOpt!cmbValues="PF",tblMonthly!pf_td,IIf(forms!frmGntOpt!cmbValues="Quantity",tblMonthly!quan_td,IIf(forms!frmGntOpt!cmbValues="Earned
Cost",tblMonthly!ebudgt_td,IIf(forms!frmGntOpt!cmbValues="Actual
Cost",tblMonthly!actualttd,IIf(forms!frmGntOpt!cmbValues="Earned
Hours",tblMonthly!whourerntd,tblMonthly!whouracttd))))) AS [Values]
SELECT IIf(forms!frmGntOpt!cmbGrpBy="CMIS
ID",tblActMap!Act,IIf(forms!frmGntOpt!cmbGrpBy="JV
ID",tblActMap!JVID,tblActMap!Srt3)) AS Description
FROM tblJVTitles RIGHT JOIN (tblCostOwners RIGHT JOIN (tblCMIS_Type RIGHT
JOIN (tblCMIS_Craft RIGHT JOIN (tblCMIS_Cat RIGHT JOIN ((tblMonthly LEFT JOIN
tblActMap ON tblMonthly.act = tblActMap.Act) LEFT JOIN tblProjInfo ON
tblMonthly.period = tblProjInfo.CMIS_Mo) ON tblCMIS_Cat.Str1 =
tblActMap.Srt1) ON tblCMIS_Craft.Srt3 = tblActMap.Srt3) ON tblCMIS_Type.Srt2
= tblActMap.Srt2) ON tblCostOwners.CO_Ini = tblActMap.CstOwnIni) ON
tblJVTitles.JVID = tblActMap.JVID
GROUP BY IIf(forms!frmGntOpt!cmbGrpBy="CMIS
ID",tblActMap!Act,IIf(forms!frmGntOpt!cmbGrpBy="JV
ID",tblActMap!JVID,tblActMap!Srt3))
ORDER BY IIf(forms!frmGntOpt!cmbGrpBy="CMIS
ID",tblActMap!Act,IIf(forms!frmGntOpt!cmbGrpBy="JV
ID",tblActMap!JVID,tblActMap!Srt3))
PIVOT
IIf(forms!frmGntOpt!CmbDteFmt="Calendar",tblProjInfo!Period_Dte,tblProjInfo!Period);

======

Thanks in advance!
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

CrossTab Help! 6
Make Table Query creates wrong field types... 8
DATABASE REPORT/CROSSTAB QUERY HELP 2

Top