Sorting Date Columns in Crosstab Query

[PaulA]

I want to sort the columns of a crosstab query that is displaying date values formated "mmm yy" and covers data from from that one year.

I want the sort to be based on time--earliest month and year first and latest month and year last. It seems that queries always sort by alpha numeric display so that Aug will always be the first column even if it isn't the first month of data.

Also, ColumnHeadings property won't work as the start and end dates of the query will regularly change.

Any suggestions?

Thanks.

 

[namliam]

I am unsure but have you tried the In statement for the crosstab? This forces columns to be present even if there is no data, i am unsure if it forces a sort of the columns as well...

Regards

The Mailman

 

[Jsam69]

In Statement?

I'm having the same exact problem, but my date range is from one year ago to today.

Can you please explain the IN Statement some more?

Thanks!

 

[namliam]

Add a predefined list to your IN clause.

An access pivot query goes kindoff like so:
Transform
Select
From
Where
group by
Pivot
In ()

Simply fill in the "In" clause

 

[April15Hater]

Hi guys,
I'm having a similar problem. I think I'm pretty close after I added the IN clause, but it still isn't sorting by my order by column. In the following code, I'm trying to get it to sort by the "Aerial" column:

TRANSFORM Sum(tblProductionInputDetail.ProductionUnits) AS SumOfProductionUnits
SELECT tblProductionInput.ProductionID, Left(tblProductionInput.InvoiceNotes,10) AS Notes, 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 = tblProductionInput.FunctionID) AND (tblFunction.FunctionID = tblContractorFunction.FunctionID)) ON tblClient.ClientID = tblJob.ClientID
WHERE (((IsNull([ClosingDate]))=True) AND ((tblJob.JobID)=27) AND ((IsNull([Hierarchy]))=False And (IsNull([Hierarchy]))=False) AND ((IsNull([Finalizedate]))=False) AND ((tblContractorFunction.FunctionType)="aerial" Or (tblContractorFunction.FunctionType)="underground" Or (tblContractorFunction.FunctionType)="unit") AND ((tblProductionTracking.TrackingOnly)=False))
GROUP BY tblProductionInput.ProductionID, Left(tblProductionInput.InvoiceNotes,10), tblFunction.FunctionID, tblJob.JobID, tblClient.ClientID, tblProductionTracking.TrackingNumber, [ContractorLast] & " ," & Left([ContractorFirst],1), tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate, IsNull([Hierarchy]), IsNull([ClosingDate]), IsNull([Finalizedate])
Order by AERIAL
PIVOT tblContractorFunction.FunctionType IN ("AERIAL", "UNDERGROUND", "UNIT");

 

[namliam]

"AERIAL", "UNDERGROUND", "UNIT"

Is already alphabetic order??

Please post SQL in a readable format... something like below

TRANSFORM Sum(tblProductionInputDetail.ProductionUnits) AS SumOfProductionUnits
SELECT tblProductionInput.ProductionID
,      Left(tblProductionInput.InvoiceNotes,10) AS Notes
,      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 = tblProductionInput.FunctionID) 
                                           AND (tblFunction.FunctionID = tblContractorFunction.FunctionID)) 
                                       ON tblClient.ClientID = tblJob.ClientID
WHERE (((IsNull([ClosingDate]))=True) 
  AND ((tblJob.JobID)=27) 
  AND ((IsNull([Hierarchy]))=False 
  And (IsNull([Hierarchy]))=False) 
  AND ((IsNull([Finalizedate]))=False) 
  AND ((tblContractorFunction.FunctionType)="aerial" 
    Or (tblContractorFunction.FunctionType)="underground" 
    Or (tblContractorFunction.FunctionType)="unit") 
  AND ((tblProductionTracking.TrackingOnly)=False))
GROUP BY tblProductionInput.ProductionID, Left(tblProductionInput.InvoiceNotes,10), tblFunction.FunctionID, tblJob.JobID, tblClient.ClientID, tblProductionTracking.TrackingNumber, [ContractorLast] & " ," & Left([ContractorFirst],1), tblProductionInput.CompleteDate, tblProductionInput.FinalizeDate, IsNull([Hierarchy]), IsNull([ClosingDate]), IsNull([Finalizedate])
Order by AERIAL
PIVOT tblContractorFunction.FunctionType IN ("AERIAL", "UNDERGROUND", "UNIT");

 

[April15Hater]

Thanks for the advice on the readable format I'll def start doing that.

The Aerial, Underground and Unit are the crosstab columns and the crosstab values are numeric. I'm just trying to get it to order by the numeric values in the Aerial Column.

Before:
Notes Tracking Contractor CompleteDate FinalizeDate Aerial Underground Unit
Note1 123-123 J, Doe 01/01/2009 01/02/2009 11 25 0
Note1 123-124 J, Doe 01/01/2009 01/02/2009 10 25 0
Note1 123-125 J, Doe 01/01/2009 01/02/2009 12 25 0

After:
Notes Tracking Contractor CompleteDate FinalizeDate Aerial Underground Unit
Note1 123-124 J, Doe 01/01/2009 01/02/2009 10 25 0
Note1 123-123 J, Doe 01/01/2009 01/02/2009 11 25 0
Note1 123-125 J, Doe 01/01/2009 01/02/2009 12 25 0

 

[namliam]

The "In" part is for when you are creating columns, not rows...

In this case you are making rows, your only solution is to make a new query. Use the crosstab as the source and sort that query.
Unfortunaly Crosstabs (still) cannot be sorted.

 

[DCrake]

If you add the actual date to the crosstab query and sort on that but do not show it in your results it should sort it correctly. Other than that you would have to include the year in your In() clause

In("Apr 08","May 08","Jun 08",etc)

David

 

[April15Hater]

I got ya. I wish they would make those darn things sortable, that's a pretty big downfall if you ask me!