Order By SQL Union Query

Mississippi Girl

Board Regular
Joined
Oct 27, 2005
Messages
148
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!!!!!!

Code:
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;
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,178
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

Board Regular
Joined
Oct 27, 2005
Messages
148
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.

Code:
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");
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 27, 2005
Messages
148
I got that syntax from the Internet, of course! :ROFLMAO:

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

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top