By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

union query, different WHERE, same table

P: 2
Hi, this is my first post.
I use Access XP.

I have a single table called Movies containing the following fields:
ID, Title, Genre, Rating, Location, Date

I want a datasheet with the Titles that have a date sorted in Descending order by date, then the Titles that don't have a date sorted by Title in Ascending order.

I have this:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Title] & " " & "(" & [Rating] & ")" AS Expr1
  2. FROM Movies
  3. WHERE (((Movies.Date) Is Not Null) AND ((Movies.Genre)="Drama"))
  4. ORDER BY Movies.Date DESC
  5. UNION ALL
  6. SELECT [Title] & " " & "(" & [Rating] & ")" AS Expr1
  7. FROM Movies
  8. WHERE (((Movies.Date) Is Null) AND (((Movies.Genre)="Drama"))
  9. ORDER BY Movies.Date ASC;
I keep getting an error about the order by clause not containing fields that are in the select clause.

What am I doing wrong?
Thanks,
Chuck
Jan 18 '09 #1
Share this Question
Share on Google+
3 Replies


P: 2
I've solved it!
The proper code should be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Title, Rating, Date
  2. FROM Movies
  3. WHERE (((Movies.Genre)="Drama")) AND (((Movies.Date) Is Not Null))
  4. UNION ALL SELECT Title, Rating, Date
  5. From Movies
  6. WHERE (((Movies.Genre)="Drama")) AND (((Movies.Date) Is Null))
  7. ORDER BY Date DESC , Title;
  8.  
It's just a simple matter to format my report now, and that is done.
Yea ME!
Jan 18 '09 #2

NeoPa
Expert Mod 15k+
P: 31,494
You've posted your question in the "insights" area.

I've moved it to the "questions" area so that it may get some interest.
Jan 19 '09 #3

NeoPa
Expert Mod 15k+
P: 31,494
I suspect you don't need anything quite so complicated.

Try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Title],
  2.        [Rating],
  3.        [Date]
  4.  
  5. FROM Movies
  6.  
  7. WHERE [Genre]='Drama'
  8.  
  9. ODER BY [Date] DESC,
  10.         [Title]
Jan 19 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.