SQL ORDER BY Feature

[dsellers1]

I'm trying to ORDER BY the first column in my Union Query. The problem is that the first column is a formatted field. Everything I have tried produces errors. Below is a sample of what I have.

I have tried Order By:
Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting' Syntax Error (missing operator)
Format([Month Reporting],"mmmm yyyy") Error stating the field is not selected by the query
[Month Reporting] Error stating the field is not selected by the query

SQL
SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsUS]
ORDER BY [Month Reporting]

Union ALL SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsCAD]
ORDER BY [Month Reporting]

UNION ALL SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsPR]
ORDER BY [Month Reporting];


Thoughts? Suggestions?

 

[vbaInet]

The Order By clause will work on the entire dataset, not on the individual queries, so your query should be:

SELECT Format([Month Reporting],"mmmm yyyy") AS [Month Reporting],[Portfolio]
From [qryRetrievalTotalsUS]

Union ALL 

SELECT Format([Month Reporting],"mmmm yyyy") AS [Month Reporting],[Portfolio]
From [qryRetrievalTotalsCAD]

UNION ALL 

SELECT Format([Month Reporting],"mmmm yyyy") AS [Month Reporting],[Portfolio]
From [qryRetrievalTotalsPR]
ORDER [Month Reporting];

 

[dsellers1]

That's good to know about the Order By expression. However your code produces a circular reference error, possibly because of the "AS [Month Reporting]" statement as opposed to "AS 'Month Reporting'".

Regardless, using the code below with AS 'Month Reporting', I still receive the error stating:

The ORDER BY expression ([Month Reporting]) includes fields that are not selected by the query. Only those fields requested by the query can be included in an ORDER BY expression

 

[vbaInet]

The circular reference is happening within one of your three queries, not the UNION ALL query. Look through your queries and take out the circular references.

Order By 'Month Reporting' is not the right syntax. Order By [Month Reporting] is the correct syntax.

By the way, I missed out the BY in the ORDER BY line. I'm sure you noticed.

 

[dsellers1]

Thanks again for the response. I was able to catch the missed "BY"

You stated, "Order By 'Month Reporting' is not the right syntax. Order By [Month Reporting] is the correct syntax.". I can't disagree with that. What I was mentioning was that the first line of each statement should read:

SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]

as opposed to

SELECT Format([Month Reporting],"mmmm yyyy") AS [Month Reporting],[Portfolio]

When I put the Month Reporting in brackets, that is when I receive the circular reference error. I can get all of that to work though. It's when I try to ORDER BY [Month Reporting] that I receive the error mentioned below in red

 

[vbaInet]

I understand what you're saying. What I mentioned is how you write a field name that has spaces, not 'Month Reporting'.

For your own clarification, use this instead:

SELECT Format([Month Reporting],"mmmm yyyy") AS Reporting, [Portfolio]

Change all the other ones too and use ORDER BY Reporting. You should still get the circular reference problem.

 

[dsellers1]

You are right. My stubbornness is coming out and I'm only seeing what I wanted to see. Never thinking about the space problem. I got it now. Just took a slap in the face to see it. Thanks again!

 

[vbaInet]

So to solve your referencing problem, you will need to look at your queries, I believe you have created a circular reference within one or more of your queries.

 

[dsellers1]

I was definitely creating the circular in the union.

Since my field was listed as 'Month Reporting', I simply put that in brackets to indicate one identifier

Here's what the final union looks like...and it worked

SELECT Format([Month Reporting],"mmmm yyyy") AS 'Month Reporting',[Portfolio]
From [qryRetrievalTotalsPR]
ORDER BY ['Month Reporting'];

Once again, thanks for the help!

 

[vbaInet]

No problemo!