Error 3351 problem

The ORDER BY expression includes fields that are not selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression. (Error 3351)
I created the Union query
the every part of the query is working fine
it does retrieve the right number
As soos as i run the union query it gives me the error above
what seems to be a problem
Roman FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roman FAuthor Commented:
or may I use the following instead of Union query
is this possible to run a set of queries?
select * from table A
select * from table B
select * from (select * from table A)
select * from (select * from table B)
0
Rey Obrero (Capricorn1)Commented:

select a.* From
(
select * from [Table A]
union All
Select * from [table b]
) as a
0
Roman FAuthor Commented:
thank you, but what seems to be a problem with my union query
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
i don't see your union query.
0
Roman FAuthor Commented:
here we go
SELECT 
          TOP 1 [tableA].[Date received 1] as [Latest Date], 
          count ([tableA].[Date received 1]) as [Count],
          Last ((select count(*) from [tableA])) as Total
FROM 
          [tableA]
group by  [tableA].[Date received 1] 
order by   [tableA].[Date received 1]  desc ;

UNION 


SELECT 
          TOP 1 [tableB].[Date received] as [Latest Date],
          Count ([tableB].[Date received]) as [Count],
          Last ((select count(*) from [tableB]))  as Total
FROM 
          [tableB]
group by  [tableB].[Date received]
order by   [tableB].[Date received] desc ;

Open in new window

0
Roman FAuthor Commented:
1 part of union return ( if run separately)
Latest Date      Count      Total
3/22/2010      29      377
2 part of union return ( if run separately)
Latest Date      Count      Total
3/22/2010      1      10
0
Rey Obrero (Capricorn1)Commented:
SELECT  
          TOP 1 [tableA].[Date received 1] as [Latest Date],  
          count ([tableA].[Date received 1]) as [Count],
          Last ((select count(*) from [tableA])) as Total
FROM  
          [tableA]
group by  [tableA].[Date received 1]  

UNION  
 
SELECT  
          TOP 1 [tableB].[Date received] as [Latest Date],
          Count ([tableB].[Date received]) as [Count],
          Last ((select count(*) from [tableB]))  as Total
FROM  
          [tableB]
group by  [tableB].[Date received]
order by 1  desc
0
Roman FAuthor Commented:

it gave the results :
which is not right, should be both 3/22/2010 or may be somthing else i am doing wrong
Latest Date      Count      Total
3/22/2010      29      377
3/12/2010      1      10
0
Rey Obrero (Capricorn1)Commented:
save the first query as query1, and the second as query2,
then create a union query


SELECT * from query1
union
select * from query2
0
Roman FAuthor Commented:
i do that right now, but i thought i could do everything at once...
what seems to be a problem
0
Roman FAuthor Commented:
HOw about if you look at this
First i run the query1

then I run the final query2
how to combine them?
my query1

SELECT 
"1" as [Error],
TableA.[Date received 1], 
count(TableA.[Date received 1] ) as [count]
FROM
TableA
group by TableA.[Date received 1]

UNION 

SELECT 
"2" as [Error],
TableB.[Date received],
count(TableB.[Date received])  as [count]
FROM 
TableB
group by TableB.[Date received];





Query2
SELECT 
query1.Error, 
Last(query1.[Date received 1]) AS [Last Date], 
Last(query1.count) AS LastOfcount, Sum(query1.count) AS SumOfcount1
FROM query1
GROUP BY query1.Error;

Open in new window

0
Rey Obrero (Capricorn1)Commented:
you can try and see the result
0
Roman FAuthor Commented:
no, i want to combine them in one query, it does not work for me
0
Rey Obrero (Capricorn1)Commented:
is there a problem doing this

save the first query as query1, and the second as query2,
then create a union query


SELECT * from query1
union
select * from query2
0
Roman FAuthor Commented:
i am doing that already , but i want only one query needs to be run
0
Rey Obrero (Capricorn1)Commented:
sorry, but i think, that is the only way you can do that to satisfy the order by clause in both queries
0
Roman FAuthor Commented:
Ok, you are saying that it is not possible to combine two queries in the one?
0
Roman FAuthor Commented:
May be you do not understand what i want, i want to combine two queries
from 03/23/10 01:37 PM, ID: 28385223          in one.
0
Rey Obrero (Capricorn1)Commented:
not in this case..  
0
Roman FAuthor Commented:
Unfortunately, it it working and i did combined two queries into one
It took me a while, but i want to thank you for the "developing"  of my mind
All points are yours, the only thing i would like to ask you :
is this possible to create macro and run at any time and where to start?
SELECT  
A.Error,  
Last(A.[Date received 1]) AS [Last Date],  
Last(A.count) AS LastOfcount, 
Sum(A.count) AS SumOfcount1 
FROM 
(
SELECT  
"1" as [Error], 
TableA.[Date received 1],  
count(TableA.[Date received 1] ) as [count] 
FROM 
TableA 
group by TableA.[Date received 1] 
 
UNION  
 
SELECT  
"2" as [Error], 
TableB.[Date received], 
count(TableB.[Date received])  as [count] 
FROM  
TableB 
group by TableB.[Date received]
) A 
 
GROUP BY A.Error;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.