A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Reload this Page Dinamically Build a CrossTab
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dinamically Build a CrossTab


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old July 11th, 2008, 06:16 PM posted to microsoft.public.access.queries
John
external usenet poster
  
Posts: 2,649
Default Dinamically Build a CrossTab
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_t d,IIf(forms!frmGntOpt!cmbValues="Quantity",tblMont hly!quan_td,IIf(forms!frmGntOpt!cmbValues="Earned
Cost",tblMonthly!ebudgt_td,IIf(forms!frmGntOpt!cmb Values="Actual
Cost",tblMonthly!actualttd,IIf(forms!frmGntOpt!cmb Values="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",tblProjIn fo!Period_Dte,tblProjInfo!Period);

======

Thanks in advance!

--
John
  #2  
Old July 11th, 2008, 06:45 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
  
Posts: 10,767
Default Dinamically Build a CrossTab
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" wrote:

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_t d,IIf(forms!frmGntOpt!cmbValues="Quantity",tblMont hly!quan_td,IIf(forms!frmGntOpt!cmbValues="Earned
Cost",tblMonthly!ebudgt_td,IIf(forms!frmGntOpt!cmb Values="Actual
Cost",tblMonthly!actualttd,IIf(forms!frmGntOpt!cmb Values="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",tblProjIn fo!Period_Dte,tblProjInfo!Period);

======

Thanks in advance!

--
John
  #3  
Old July 11th, 2008, 07:18 PM posted to microsoft.public.access.queries
John
external usenet poster
  
Posts: 2,649
Default Dinamically Build a CrossTab
Karl,

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


"KARL DEWEY" wrote:

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" wrote:

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_t d,IIf(forms!frmGntOpt!cmbValues="Quantity",tblMont hly!quan_td,IIf(forms!frmGntOpt!cmbValues="Earned
Cost",tblMonthly!ebudgt_td,IIf(forms!frmGntOpt!cmb Values="Actual
Cost",tblMonthly!actualttd,IIf(forms!frmGntOpt!cmb Values="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",tblProjIn fo!Period_Dte,tblProjInfo!Period);

======

Thanks in advance!

--
John
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:34 PM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.