Change order of Crosstab Columns

[April15Hater]

Hi guys

I'm hoping this is even possible. I have a crosstab query and I noticed the Column Headings and Values are always shown last. I have another field that I need to show after the values. How can I alter my code to do that?

Thanks,

Joe

TRANSFORM Sum(tblProductionInputDetail.ProductionUnits) AS SumOfProductionUnits 
SELECT tblProductionInput.ProductionID as PID, tblProductionTracking.TrackingNumber as Tracking, [ContractorLast] & " ," & Left([ContractorFirst],1) AS Contractor, tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate 
FROM tblClient INNER JOIN ((tblJob INNER JOIN tblFunction ON tblJob.JobID = tblFunction.JobID) INNER JOIN (tblFunctionTracking RIGHT JOIN (tblContractorFunction INNER JOIN (((tblProductionInput INNER JOIN tblContractor ON tblProductionInput.ContractorID = tblContractor.ContractorID) INNER JOIN tblProductionInputDetail ON tblProductionInput.ProductionID = tblProductionInputDetail.ProductionID) INNER JOIN tblProductionTracking ON tblProductionInput.ProductionID = tblProductionTracking.ProductionID) ON tblContractorFunction.ContractorFunctionID = tblProductionInputDetail.ContractorFunctionID) ON tblFunctionTracking.FunctionTrackingID = tblProductionTracking.FunctionTrackingID) ON (tblFunction.FunctionID = tb
lProductionInput.FunctionID) AND (tblFunction.FunctionID = tblContractorFunction.FunctionID)) ON tblClient.ClientID = tblJob.ClientID 
WHERE (IsNull([ClosingDate]) = True) AND (tblClient.ClientID = 18 ) and (IsNull([Hierarchy]) = False) And (IsNull([Finalizedate]) = False) And (tblContractorFunction.FunctionType = "aerial" Or tblContractorFunction.FunctionType = "underground" Or tblContractorFunction.FunctionType = "unit") AND (TrackingOnly=False) 
GROUP BY tblProductionInput.ProductionID, tblFunction.FunctionID, tblJob.JobID, tblClient.ClientID, tblProductionTracking.TrackingNumber, [ContractorLast] & " ," & Left([ContractorFirst],1), tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate, IsNull([Hierarchy]), IsNull([ClosingDate]), IsNull([Finalizedate]) 
PIVOT tblContractorFunction.FunctionType;

 

[Banana]

Off the top head, but I believe ORDER BY is either illegal or only works across rows but not columns. To force a particular ordering for column heading, you would use a sub-query with ORDER BY inside then pivot upon that.

 

[boblarson]

Based on a project that I worked on in my previous job (in fact the one that made me go "mad" and leave) it required us to do a UNION query to get the data at the end of the crosstab (and we had crosstabs that could have up 1 to 120 columns based on a user selection). It was not fun and not pretty. I didn't come up with the solution, one of the contractors from Russia did.

 

[Banana]

Wait, are we talking something like this:

 	Col1 	Col2 	Col3 	Total
Row1 	 1 	 2 	 3 	 5
Row2 	 1 	 2 	 4 	 7
Row3 	 1 	 3 	 9 	13
Total 	 3 	 7 	16 	25

If that's the case, then I'd probably just make the Total column a separate subreport or subform based on the same query filtering the rows. (Ditto for the bottom total rows; same crosstab but with value aggregated.

 

[April15Hater]

That's actually the direction I was working in, but have never had luck with unions. I gave it a few tries, but I don't know what I'm doing wrong.

TRANSFORM Sum(tblProductionInputDetail.ProductionUnits) AS SumOfProductionUnits 
SELECT tblProductionInput.ProductionID as PID, tblProductionTracking.TrackingNumber as Tracking, [ContractorLast] & " ," & Left([ContractorFirst],1) AS Contractor, tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate 
FROM tblClient INNER JOIN ((tblJob INNER JOIN tblFunction ON tblJob.JobID = tblFunction.JobID) INNER JOIN (tblFunctionTracking RIGHT JOIN (tblContractorFunction INNER JOIN (((tblProductionInput INNER JOIN tblContractor ON tblProductionInput.ContractorID = tblContractor.ContractorID) INNER JOIN tblProductionInputDetail ON tblProductionInput.ProductionID = tblProductionInputDetail.ProductionID) INNER JOIN tblProductionTracking ON tblProductionInput.ProductionID = tblProductionTracking.ProductionID) ON tblContractorFunction.ContractorFunctionID = tblProductionInputDetail.ContractorFunctionID) ON tblFunctionTracking.FunctionTrackingID = tblProductionTracking.FunctionTrackingID) ON (tblFunction.FunctionID = tb
lProductionInput.FunctionID) AND (tblFunction.FunctionID = tblContractorFunction.FunctionID)) ON tblClient.ClientID = tblJob.ClientID 
WHERE (IsNull([ClosingDate]) = True) AND (tblClient.ClientID = 1 ) and (IsNull([Hierarchy]) = False) And (IsNull([Finalizedate]) = False) And (tblContractorFunction.FunctionType = "aerial" Or tblContractorFunction.FunctionType = "underground" Or tblContractorFunction.FunctionType = "unit") AND (TrackingOnly=False) 
GROUP BY tblProductionInput.ProductionID, tblFunction.FunctionID, tblJob.JobID, tblClient.ClientID, tblProductionTracking.TrackingNumber, [ContractorLast] & " ," & Left([ContractorFirst],1), tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate, IsNull([Hierarchy]), IsNull([ClosingDate]), IsNull([Finalizedate]) 
PIVOT tblContractorFunction.FunctionType 
UNION 
SELECT InvoiceNote FROM tblClient INNER JOIN ((tblJob INNER JOIN tblFunction ON tblJob.JobID = tblFunction.JobID) INNER JOIN (tblFunctionTracking RIGHT JOIN (tblContractorFunction INNER JOIN (((tblProductionInput INNER JOIN tblContractor ON tblProductionInput.ContractorID = tblContractor.ContractorID) INNER JOIN tblProductionInputDetail ON tblProductionInput.ProductionID = tblProductionInputDetail.ProductionID) INNER JOIN tblProductionTracking ON tblProductionInput.ProductionID = tblProductionTracking.ProductionID) ON tblContractorFunction.ContractorFunctionID = tblProductionInputDetail.ContractorFunctionID) ON tblFunctionTracking.FunctionTrackingID = tblProductionTracking.FunctionTrackingID) ON (tblFunction.FunctionID = tblProductionInput.FunctionID) AND (tblFunction.FunctionID = tblContractorFunction.FunctionID)) ON tblClient.ClientID = tblJob.ClientID 
WHERE (IsNull([ClosingDate]) = True) AND (tblClient.ClientID = 1 ) and (IsNull([Hierarchy]) = False) And (IsNull([Finalizedate]) = False) And (tblCo
ntractorFunction.FunctionType = "aerial" Or tblContractorFunction.FunctionType = "underground" Or tblContractorFunction.FunctionType = "unit") AND (TrackingOnly=False) 
GROUP BY tblProductionInput.ProductionID, tblFunction.FunctionID, tblJob.JobID, tblClient.ClientID, tblProductionTracking.TrackingNumber, [ContractorLast] & " ," & Left([ContractorFirst],1), tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate, IsNull([Hierarchy]), IsNull([ClosingDate]), IsNull([Finalizedate]);

 

[April15Hater]

Banana,
No totals. I'm using it in a list box, and it's for an approval process. The crosstab will show all of the columns/values needed for the user to make the determination of whether to approve it, but at the end of it, I was going to have a notes column that is going to be rather wide and not always necessary to view. My alternative is to show the notes wide in the beginning of the query which is just tacky.

 

[boblarson]

My alternative is to show the notes wide in the beginning of the query which is just tacky.

or maybe just double-click to show the note in a popup????

 

[April15Hater]

That is an alternative however it's multiselect, and the user would lose everything that was already selected. May be my only out though...

 

[boblarson]

That is an alternative however it's multiselect, and the user would lose everything that was already selected. May be my only out though...

Okay, how about this option then -

you could have a button next to the listbox and open a form that has the notes populated for the records selected. (just an alternative idea to try to help)

 

[April15Hater]

Thanks Bob! I think that's the best way to go since I can't get this stupid union working...

Thanks again guys!