UNION THEN SAVE QUERY AS TABLE

[westex96]

HI EXPERT,

I AM COMPLETING THE FOLLOWING COMMAND TO UNION 50 TABLES. THE DATA POINTS EXCEED 1.5 MILLION AFTER THE QUERY COMPLETES. I NEED TO GET THIS NEW DATA INTO A TABLE. ANY SUGGESTIONS? IF I TRY TO USE THE MAKE TABLE QUERY WITH THE UNION COMMAND IT STILL CHANGES BACK TO A UNION QUERY.

SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351A UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351B UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351C UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO352A...AND SO ON FOR 50 TABLES.

THANKS FOR YOUR HELP!

RAY

 

[r937]

make table" ==> microsoft access, right?

do a make table query on the first table only --

SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR
INTO mynewtable
FROM Sector_Power_RO351A

then run an append query for the union of the others --

INSERT INTO mynewtable
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR
FROM Sector_Power_RO351B UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR
FROM Sector_Power_RO351C UNION
...



r937.com | rudy.ca

 

[westex96]

Hi 937,
Thank you so much for taking the time to lend a hand. Yes this is Access.

The idea behind this approach is excellent. I made the new table (RO351_RO409C_TABLE) with no problem.

Now when I run the INSERT INTO Approach I get an error for the UNION command.

If I drop the UNION command I can only append one line at a time. Any additional Ideas?

Trying to Run get error on first UNION

INSERT INTO RO351_RO409C_TABLE
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351B UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351C UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO352A UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO352B UNION...

I can run this one at a time.

INSERT INTO RO351_RO409C_TABLE
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351B;

Thank you!

Ray

 

[r937]

write the UNION query and save it as a query

SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR
FROM Sector_Power_RO351B UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR
FROM Sector_Power_RO351C UNION
...

then run the INSERT query using the saved union query like this --

INSERT INTO mynewtable
SELECT * FROM savedquery

r937.com | rudy.ca

 

[AlexCuse]

westex96 - you need to place your union query in parentheses and treat it as a derived table. Like this:

insert into RO351_RO409C_TABLE
SELECT a.Longitude, a.Latitude, a.Sector_Rx_Power, a.Sector
from
(
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351B UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO351C UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO352A UNION
SELECT Longitude, Latitude, Sector_Rx_Power, SECTOR FROM Sector_Power_RO352B
) a

hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[westex96]

Thank you so much Alex...this worked great! I really appreciate your help!

Cheers,

Ray

 

[AlexCuse]

Glad it worked

I suggest you go to this forum next time you have an Access question: forum701, you will probably have more luck there.

Also, for the record - r937's suggestion was that you save the union query (at which point access can reference it the same way one would reference a table), then run an insert selecting from that query. This is basically the same thing that the parentheses/derived table alias did for you, but without saving the query.



Ignorance of certain subjects is a great part of wisdom