Dynamic Fields in Crosstab

  • Thread starter Claudette Hennessy
  • Start date
C

Claudette Hennessy

Recently Karl Dewey posted a solution to dynamic fields to add In
("Spring","Summer","Fall") to the PIVOT line.

PARAMETERS [Forms]![frmDialogContractMailMerge]![cboYear] Short;
TRANSFORM First(tblContract.SendContract) AS FirstOfSendContract
SELECT tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
FROM tblContract
WHERE (((Get_ShowName([ShowID])) In ("Spring","Summer","Fall")) AND
((tblContract.ShowYear)=[Forms]![frmDialogContractMailMerge]![cboYear]))
GROUP BY tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
PIVOT Get_ShowName([ShowID]);

I get syntax complaints combining the In clause with the function.

I need the result:

ShopID ShowYear Spring Summer Fall
1 2009 Y
2 2009 Y
.....

How can I force the crosstab to list the other fields which are presently
not populated?
this query is joined to another which fails unless the fields "Summer" and
"Fall" are present in the crosstab.:

SELECT tblDealer.ShopID, tblDealer.ShopName, qxttbllContract.Spring,
qxttbllContract.Summer, qxttbllContract.Fall, qxttbllContract.DateMailed,
qxttbllContract.ShowYear
FROM tblDealer LEFT JOIN qxttbllContract ON tblDealer.ShopID =
qxttbllContract.ShopID;

thank you in advance,
Claudette
 
K

KARL DEWEY

Try changing to this ---
PIVOT Get_ShowName([ShowID]) In ("Spring","Summer","Fall");

What is Get_ShowName([ShowID]) - a table and field? That syntax is new
to me.
 
C

Claudette Hennessy

That works!!, Thank you! Get_ShowName is a function, I pass it the
ShowID, I get the ShowName. I am never sure if I need the []'s or not.
Sometimes the compiler puts them in if I don't.

Claudette
KARL DEWEY said:
Try changing to this ---
PIVOT Get_ShowName([ShowID]) In ("Spring","Summer","Fall");

What is Get_ShowName([ShowID]) - a table and field? That syntax is
new
to me.

--
KARL DEWEY
Build a little - Test a little


Claudette Hennessy said:
Recently Karl Dewey posted a solution to dynamic fields to add In
("Spring","Summer","Fall") to the PIVOT line.

PARAMETERS [Forms]![frmDialogContractMailMerge]![cboYear] Short;
TRANSFORM First(tblContract.SendContract) AS FirstOfSendContract
SELECT tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
FROM tblContract
WHERE (((Get_ShowName([ShowID])) In ("Spring","Summer","Fall")) AND
((tblContract.ShowYear)=[Forms]![frmDialogContractMailMerge]![cboYear]))
GROUP BY tblContract.ShopID, tblContract.ShowYear, tblContract.DateMailed
PIVOT Get_ShowName([ShowID]);

I get syntax complaints combining the In clause with the function.

I need the result:

ShopID ShowYear Spring Summer Fall
1 2009 Y
2 2009 Y
.....

How can I force the crosstab to list the other fields which are presently
not populated?
this query is joined to another which fails unless the fields "Summer"
and
"Fall" are present in the crosstab.:

SELECT tblDealer.ShopID, tblDealer.ShopName, qxttbllContract.Spring,
qxttbllContract.Summer, qxttbllContract.Fall, qxttbllContract.DateMailed,
qxttbllContract.ShowYear
FROM tblDealer LEFT JOIN qxttbllContract ON tblDealer.ShopID =
qxttbllContract.ShopID;

thank you in advance,
Claudette
Click to expand...
Click to expand...
 
Top