Help! Pivot/crosstab query

[kbmosher]

I am so frustrated I could spit. I have been trying to use pivot queries for 5 years and NEVER been able to make one that is useful. I found a perfect use and I can get the data EXACTLY the way I want it using the pivot table view button .


Using that method I can create the EstimatingCopyToQtysPivotQry below:


unfortunately I need to open this as a recordset but even though it is saved to open as a PivotTable as the default opening this query with another query opens a standard datasheet view.

I tried writing a Crosstab Query but it does not allow multiple columns under each Pivot column as you can see by Query Test22 above.. I cannot find anything online that gives me the syntax to write a query like EstimatingCopyToQtysPivotQry and Microsoft Access stores this a a standard query. It does not store the transform/pivot.

To make it clear, I have 5 categories 'B, G, L, M, and S'. These are one column of the each row. I then have 14 quantity fields that need to summed by those categories (The Rows are grouped by an ID #)

Here is the SQL for the Test22 Query shown above:

 TRANSFORM Sum(JobQuoteCalcs.Jqc_SuAmtQty1) AS SumOfJqc_SuAmtQty1
SELECT JobQuoteCalcs.Jqc_QuoteLink
FROM JobQuoteCalcs
GROUP BY JobQuoteCalcs.Jqc_QuoteLink
PIVOT JobQuoteCalcs.Jqc_LineType;

Here is the SELECT Query that is stored for EstimatingCopyToQtysPivotQry shown above:

SELECT JobQuoteCalcs.Jqc_QuoteLink, IIf([Jqc_LineType]="G","M",[Jqc_LineType]) AS LineType, JobQuoteCalcs.Jqc_SuAmtQty1, JobQuoteCalcs.Jqc_EaAmtQty1, JobQuoteCalcs.Jqc_SuAmtQty2, JobQuoteCalcs.Jqc_EaAmtQty2, JobQuoteCalcs.Jqc_SuAmtQty3, JobQuoteCalcs.Jqc_EaAmtQty3, JobQuoteCalcs.Jqc_SuAmtQty4, JobQuoteCalcs.Jqc_EaAmtQty4, JobQuoteCalcs.Jqc_SuAmtQty5, JobQuoteCalcs.Jqc_EaAmtQty5, JobQuoteCalcs.Jqc_SuAmtQty6, JobQuoteCalcs.Jqc_EaAmtQty6, JobQuoteCalcs.Jqc_SuAmtQty7, JobQuoteCalcs.Jqc_EaAmtQty7
FROM JobQuoteCalcs
WHERE (((JobQuoteCalcs.Jqc_QuoteLink)=[LinkID]));

I need to know how to get multiple columns under each Jqc_LineType field in a query that a can open as a recordset in VBA.

Thanks in advance for any help
Kim

 

[namliam]

If you are opening the recordset in VBA you should be able to work with a normal query and your data in rows just as easily as you would if they were pivotted to columns.

Only way in a crosstab you can "fake" multiple columns in the headers, is to concatinate the 2 fields into one and pivot the concatinated field

 

[Simon_MT]

I will not have the correct syntax or attributes but try something along the lines of:

LineType_BQty: iif(Jqc_LineType="B",Jqc_Sum_Qty ,0)
LineType_GQty: iif(Jqc_LineType="G",Jqc_Sum_Qty ,0)
LineType_LQty: iif(Jqc_LineType="L",Jqc_Sum_Qty ,0)
LineType_MQty: iif(Jqc_LineType="M",Jqc_Sum_Qty ,0)
LineType_SQty: iif(Jqc_LineType="S",Jqc_Sum_Qty ,0)

Simon