Weird Union Dispute - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Weird Union Dispute

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-04-01 : 16:23:19

I have the followin SQL code (within MS ACCESS 2007)

SELECT * INTO TOTAL_TIX
FROM ALPHA_TICKETS UNION
SELECT * FROM GAMMA_TICKETS UNION
SELECT * FROM KAPPA_TICKETS UNION
SELECT * FROM SIGMA_TICKETS UNION
SELECT * FROM OMEGA_TICKETS UNION
GO;
But when I attempt to run it, I receive this funny statement:

"An action query cannot be used for a row source."

Since when is a SELECT statement not a welcome command in ACCESS?

Anyone with a way to work around this? II have no choice but to start the process with the amalgamation of all my ticket files.

Thanks.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 16:32:40

Remove the last UNION as there is nothing after it. I don't know if that'll fix your issue, but what you have posted is not valid SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-04-01 : 16:42:54

Hi Tara....let's presume that last UNION isn't there. I simply posted a generic version of what I was working on.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 16:50:56

How about you do one table with the INTO and then use INSERT INTO after that for the others?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-04-01 : 17:02:52

Great idea! Thanks, but I'm still curious...why is that message generated? It doesn't make sense.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 17:06:22

I don't know. I've never used Access. People don't always respond to posts in this forum, so I thought I'd offer some help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-04-01 : 17:11:23

Thanks, Tara! I appreciate your help!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-01 : 17:18:47

You're welcome.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-02 : 02:17:16

Have you tried this?

SELECT * INTO TOTAL_TIX
FROM
(
SELECT * FROM ALPHA_TICKETS UNION
SELECT * FROM GAMMA_TICKETS UNION
SELECT * FROM KAPPA_TICKETS UNION
SELECT * FROM SIGMA_TICKETS UNION
SELECT * FROM OMEGA_TICKETS
) as t

Madhivanan

Failing to plan is Planning to fail

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-04-02 : 12:22:30

Awesome idea, Madhivanan! I'll try it, Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2010-04-02 : 12:55:53

quote:
Originally posted by madhivanan

Have you tried this?

SELECT * INTO TOTAL_TIX
FROM
(
SELECT * FROM ALPHA_TICKETS UNION
SELECT * FROM GAMMA_TICKETS UNION
SELECT * FROM KAPPA_TICKETS UNION
SELECT * FROM SIGMA_TICKETS UNION
SELECT * FROM OMEGA_TICKETS
) as t

THIS WORKED!

Madhivanan

Failing to plan is Planning to fail

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL & VB obviously!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources