Full Version: Pivot Chart From Cross Tab Query In A Form
UtterAccess Forums > Microsoft® Access > Access Forms
genoma111
Hello,
I'm trying to create a form which displays a pivot chart from a parametric cross-tab query.
The parametric cross-tab query works fine.
The problem I have is with the form.
Odon't know how to dynamically change the columns (since they are different everytime)
If I create pivot chart form based on the open query, I can sort the rows column and values I want and display the Chart as expected.
But if I change the parameters, because the column headings are different, the form doesn't show anything (neither in datasheet view).
Then, my question is ¿How can I make this form to change "dynamically" the columns?
Hope I explain my self ok.
Kind Regards,
Diego
S: This is the SQL of my parametric cross tab query
CODE
PARAMETERS [Forms]![Navigation]![Text15] Long, [Forms]![Navigation]![Text17] Long;
TRANSFORM First(qryTFBSs.MatrixSim) AS FirstOfMatrixSim
SELECT DNASeq.Position, DNASeq.Nucleotide
FROM DNASeq INNER JOIN qryTFBSs ON DNASeq.Position = qryTFBSs.Position
WHERE (((DNASeq.Position) Between [Forms]![Navigation]![Text15] And [Forms]![Navigation]![Text17]))
GROUP BY DNASeq.Position, DNASeq.Nucleotide
PIVOT qryTFBSs.MatrixN;

It is based upon a Union query
CODE
SELECT DNASeq.Position, tblTF.MatrixN, tblPromTFBS.Start, tblPromTFBS.End, tblPromTFBS.CoreSim, tblPromTFBS.MatrixSim
FROM tblTF INNER JOIN (tblPromTFBS INNER JOIN (DNASeq INNER JOIN iPosPromTFBS ON DNASeq.Position = iPosPromTFBS.Position) ON tblPromTFBS.PromTFBSID = iPosPromTFBS.PromTFBSID) ON tblTF.TFID = tblPromTFBS.TFID
WHERE (((DNASeq.Position) Between [Forms]![Navigation]![Text15] And [Forms]![Navigation]![Text17]));
UNION
SELECT DNASeq.Position, tblTF.MatrixN, iPredModTFBS.Start, iPredModTFBS.End, iPredModTFBS.CoreSim, iPredModTFBS.MatSim
FROM tblTF INNER JOIN (iPredModTFBS INNER JOIN (DNASeq INNER JOIN iPosPredModTFBS ON DNASeq.Position = iPosPredModTFBS.Position) ON iPredModTFBS.PredModTFID = iPosPredModTFBS.PredModTFID) ON tblTF.TFID = iPredModTFBS.TFID
WHERE (((DNASeq.Position) Between [Forms]![Navigation]![Text15] And [Forms]![Navigation]![Text17]));
UNION
SELECT DNASeq.Position, tblTF.MatrixN, tblSNPncTFBSLost.Start, tblSNPncTFBSLost.End, tblSNPncTFBSLost.CoreSim, tblSNPncTFBSLost.MatrixSim
FROM tblTF INNER JOIN (tblSNPncTFBSLost INNER JOIN (DNASeq INNER JOIN iPosSNPncTFBSLost ON DNASeq.Position = iPosSNPncTFBSLost.Position) ON tblSNPncTFBSLost.SNPncTFBSLostID = iPosSNPncTFBSLost.SNPncTFBSLostID) ON tblTF.TFID = tblSNPncTFBSLost.TFID
WHERE (((DNASeq.Position) Between [Forms]![Navigation]![Text15] And [Forms]![Navigation]![Text17]));
UNION
SELECT DNASeq.Position, tblTF.MatrixN, tblSNPncTFBSNew.Start, tblSNPncTFBSNew.End, tblSNPncTFBSNew.CoreSim, tblSNPncTFBSNew.MatrixSim
FROM tblTF INNER JOIN (tblSNPncTFBSNew INNER JOIN (DNASeq INNER JOIN iPosSNPncTFBSNew ON DNASeq.Position = iPosSNPncTFBSNew.Position) ON tblSNPncTFBSNew.SNPncTFBSNewID = iPosSNPncTFBSNew.SNPncTFBSNewID) ON tblTF.TFID = tblSNPncTFBSNew.TFID
WHERE (((DNASeq.Position) Between [Forms]![Navigation]![Text15] And [Forms]![Navigation]![Text17]));
BananaRepublic
I do not believe you will need to use a crosstab query as the source for your PivotChart/PivotTable; you just want to let them do the "crosstabbing" for you. Therefore, I would suggest you make the Union query with a join to qryTFBS (if needed) as your source for PivotChart. You would be then able to use MatrixN as your column without needing to change it every time because of your parameter query.
oes that help?
genoma111
Hello again BananaR
Ocreated a form based upon my Union query.
The problem is the Pivot Chart shows the MatrixN results just as a Counter.
But I need the results discriminated by every single type of Matrix.
Does this makes sense?
Not sure if you were suggesting something different ???
Best Regards,
Diego
genoma111
Oh I get it!
Let me play a little bit with this, and later I will reply to you again
Thanks!
genoma111
Thank you again.
It works well.
Diego
BananaRepublic
Glad you were able to work it out! humbup:" border="0" alt="thumbup.gif" />
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.