Inserting From A Union Has Problems. 2

[Golom]

Although I've managed my way around the problem with some code, I am curious about what the problem is with the following.

I'm attempting to INSERT INTO a table with the following SQL

INSERT INTO CompletedOrders ( Code, Description, Quantity, Customer, Phone )

SELECT S.Code,S.Description,S.Quantity, S.Customer, S.Phone  
FROM SpecialOrders AS S 
Where  Datediff("ww",S.[Starting On],#10/25/2004#) Mod S.Weeks=0  
       AND S.Weeks > 0  
       AND #10/25/2004# >= S.[Starting On]

UNION ALL 

SELECT S.Code, S.Description, S.Quantity, S.Customer, S.Phone  
FROM SpecialOrders AS S 
Where  S.[Starting On] = #10/25/2004# 
       AND S.Weeks = 0

The UNION runs fine when run as a select query but when I use the same union statement as the source for an "INSERT INTO" it complains that it's an invalid UNION query ... and highlights the word UNION (not very helpful.)

Any ideas or does Access just not allow a Union as the source of an INSERT?

 

[PHV]

Either do 2 consecutives append query or do the insert from a saved union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

PHV

Yeah ... my work-around was the saved union option and it works fine.

Guess this is just one of those " ... there's no real reason ... it just doesn't work ... " things.

Thanks for the thoughts.

 

[lespaul]

I don't feel so bad now!!! If you didn't know that, there's only a minimal chance that I would know it! I recommended someone use this technique and couldn't get it to work until PHV told me that Access doesn't do UNION inside an INSERT.



Leslie

 

[JonFer]

It works if you format the Union as an "inline" view:

INSERT INTO CompletedOrders ( Code, Description, Quantity, Customer, Phone )

SELECT A.Code,A.Description,A.Quantity, A.Customer, A.Phone
FROM 

(SELECT S.Code,S.Description,S.Quantity, S.Customer, S.Phone
FROM SpecialOrders AS S 
Where  Datediff("ww",S.[Starting On],#10/25/2004#) Mod S.Weeks=0  
       AND S.Weeks > 0  
       AND #10/25/2004# >= S.[Starting On]

UNION ALL 

SELECT S.Code, S.Description, S.Quantity, S.Customer, S.Phone  
FROM SpecialOrders AS S 
Where  S.[Starting On] = #10/25/2004# 
       AND S.Weeks = 0 ) as A

John

http://www.interautomateconsulting.com

 

[Golom]

JonFer

Now there's a great idea!

And like all great ideas it's obvious when when someone else thinks of it.

Have a star.

 

[lespaul]

have another!

Leslie