Simple SQL Union query syntax question

[CTOROCK]

I have many union queries and I have no problem joining tables, however i experience trouble when I try the GROUP BY expression. I use two queries for this where I group by in the second. I know I shouldn't have to. My union query is:

select[Provider]
from Q_DoctorsFromInput
UNION ALL select [Provider Name]
from Q_DoctorsAll
ORDER BY [Provider];

I try this:
select[Provider]
from Q_DoctorsFromInput
UNION ALL select [Provider Name]
from Q_DoctorsAll
GROUP BY [Provider]
ORDER BY [Provider];

but it doesn't work? Any suggestions?

Thanks in advance for your time

"The greatest risk, is not taking one."

 

[dhookom]

Try:

select[Provider]
from Q_DoctorsFromInput
UNION 
select [Provider  Name] 
from Q_DoctorsAll
ORDER BY 1;

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[CTOROCK]

thanks Duane. It was the Union All!



"The greatest risk, is not taking one."