Order By SQL Union Query

[Mississippi Girl]

I'm joining two queries into one. Both have the field Period, which is a month. On the query results, I'd like to order the period as Oct, Nov, Dec, etc. I've tried the Order By syntax, but that doesn't work. Anyone have any ideas?

Thanks!!!!!!

SELECT [qryContractReviewCost].[Cost Element], [qryContractReviewCost].[Op Plan Description], [qryContractReviewCost].[Project Code], [qryContractReviewCost].[BLI], [qryContractReviewCost].[Period], [qryContractReviewCost].[Sum of Cost] AS [Cost], [qryContractReviewPlan].[SumofPlanned $$] AS [Plan]
FROM [qryContractReviewCost]
LEFT OUTER JOIN [qryContractReviewPlan] ON [qryContractReviewCost].BLI = [qryContractReviewPlan].BLI
AND [qryContractReviewCost].[Cost Element] = [qryContractReviewPlan].[Cost Element]
AND [qryContractReviewCost].[Op Plan Description] = [qryContractReviewPlan].[Op Plan Description]
AND [qryContractReviewCost].[Project Code] = [qryContractReviewPlan].[Project Code]
AND [qryContractReviewCost].Period = [qryContractReviewPlan].Period
UNION SELECT [qryContractReviewPlan].[Cost Element], [qryContractReviewPlan].[Op Plan Description], [qryContractReviewPlan].[Project Code], [qryContractReviewPlan].BLI, [qryContractReviewPlan].Period, [qryContractReviewCost].[Sum of Cost], [qryContractReviewPlan].[SumofPlanned $$]
FROM [qryContractReviewCost]
RIGHT OUTER JOIN [qryContractReviewPlan] ON [qryContractReviewCost].BLI = [qryContractReviewPlan].BLI
AND [qryContractReviewCost].[Cost Element] = [qryContractReviewPlan].[Cost Element]
AND [qryContractReviewCost].[Op Plan Description] = [qryContractReviewPlan].[Op Plan Description]
AND [qryContractReviewCost].[Project Code] = [qryContractReviewPlan].[Project Code]
AND [qryContractReviewCost].Period = [qryContractReviewPlan].Period;

 

[james_lankford]

what do you mean it doesn't work ?

I don't see the order by clause in your sql

what happens when you put it in and run it ?
a syntax error ?
it sorts, but not the way you want ?
it doesn't sort at all ?

 

[Mississippi Girl]

I took out what I had because I knew it wasn't right. The error I get is "Undefined Function '[qryContractReviewPlan].Period' in expression." The query doesn't run at all. Without that statement, the query runs and orders the months alphabetically.

SELECT [qryContractReviewCost].[Cost Element], [qryContractReviewCost].[Op Plan Description], [qryContractReviewCost].[Project Code], [qryContractReviewCost].[BLI], [qryContractReviewCost].[Period], [qryContractReviewCost].[Sum of Cost] AS [Cost], [qryContractReviewPlan].[SumofPlanned $$] AS [Plan]
FROM [qryContractReviewCost]
LEFT OUTER JOIN [qryContractReviewPlan] ON [qryContractReviewCost].BLI = [qryContractReviewPlan].BLI
AND [qryContractReviewCost].[Cost Element] = [qryContractReviewPlan].[Cost Element]
AND [qryContractReviewCost].[Op Plan Description] = [qryContractReviewPlan].[Op Plan Description]
AND [qryContractReviewCost].[Project Code] = [qryContractReviewPlan].[Project Code]
AND [qryContractReviewCost].Period = [qryContractReviewPlan].Period
UNION SELECT [qryContractReviewPlan].[Cost Element], [qryContractReviewPlan].[Op Plan Description], [qryContractReviewPlan].[Project Code], [qryContractReviewPlan].BLI, [qryContractReviewPlan].Period, [qryContractReviewCost].[Sum of Cost], [qryContractReviewPlan].[SumofPlanned $$]
FROM [qryContractReviewCost]
RIGHT OUTER JOIN [qryContractReviewPlan] ON [qryContractReviewCost].BLI = [qryContractReviewPlan].BLI
AND [qryContractReviewCost].[Cost Element] = [qryContractReviewPlan].[Cost Element]
AND [qryContractReviewCost].[Op Plan Description] = [qryContractReviewPlan].[Op Plan Description]
AND [qryContractReviewCost].[Project Code] = [qryContractReviewPlan].[Project Code]
AND [qryContractReviewCost].Period = [qryContractReviewPlan].Period
ORDER BY [qryContractReviewPlan].Period ("Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep"), [qryContractReviewCost].Period ("Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep");

 

[Norie]

Where did you get the ORDER BY clause from, I don't think I've ever seen anything like that?

How do you actually want things ordered?

Oops, just re-read the original post.

If you want to order like that I think you might need an expression of some sort that equates Oct to 1, Nov to 2 etc.

Then you could do the ordering based on that.

 

[Mississippi Girl]

I got that syntax from the Internet, of course!

It will work on a regular select or crosstab query, but I can't get it to work here.