append with union query?

  • Thread starter Thread starter vtj
  • Start date Start date
V

vtj

I am trying to get a union query to append records to a table. The fields
are identical and in the same order in the table I'm appending to as the
query. I get an error 'Syntax error in from clause' when I try to run this.

The SQL is: INSERT INTO [APPROP TEST] SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107];

The two queries work in the union query if I take the 'INSERT INTO [APPROP
TEST]' away from the above SQL. The table is blank to start with and there
are about 40 queries that I would like to union to make the table. I was
trying to get it to work with just two queries before adding the rest. Is
this even a possible? Or am I dreaming?
 
Try this:

INSERT INTO [APPROP TEST]
SELECT *
FROM (SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107]);
 
Works great!! Thanks so much.

Jerry Whittle said:
Try this:

INSERT INTO [APPROP TEST]
SELECT *
FROM (SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


vtj said:
I am trying to get a union query to append records to a table. The fields
are identical and in the same order in the table I'm appending to as the
query. I get an error 'Syntax error in from clause' when I try to run this.

The SQL is: INSERT INTO [APPROP TEST] SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107];

The two queries work in the union query if I take the 'INSERT INTO [APPROP
TEST]' away from the above SQL. The table is blank to start with and there
are about 40 queries that I would like to union to make the table. I was
trying to get it to work with just two queries before adding the rest. Is
this even a possible? Or am I dreaming?
Click to expand...
Click to expand...
 
Try writing the UNION query separately and saving it. Then use the saved
query as the source for the insert.

I doubt that you will be able to union forty queries. One limit you will hit
is that the query can have a maximum of 255 fields. So if the tables or
queries you are trying to union have 7 fields you are going to have 280 fields
involved in the union query which will mean the query will error with too many
fields.

A better result might be to append each table/query to your destination table.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Just for information, I don't know what to make of it but I am getting 63
fields in each query and can union 16 queries. The 63 fields are the same in
all the queries. I don't know if that means I still have just 63 fields or
if I have 1008? I agree with your append approach - I was trying not to have
to redo all 40 queries. Thanks for your reply.

John Spencer said:
Try writing the UNION query separately and saving it. Then use the saved
query as the source for the insert.

I doubt that you will be able to union forty queries. One limit you will hit
is that the query can have a maximum of 255 fields. So if the tables or
queries you are trying to union have 7 fields you are going to have 280 fields
involved in the union query which will mean the query will error with too many
fields.

A better result might be to append each table/query to your destination table.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I am trying to get a union query to append records to a table. The fields
are identical and in the same order in the table I'm appending to as the
query. I get an error 'Syntax error in from clause' when I try to run this.

The SQL is: INSERT INTO [APPROP TEST] SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107];

The two queries work in the union query if I take the 'INSERT INTO [APPROP
TEST]' away from the above SQL. The table is blank to start with and there
are about 40 queries that I would like to union to make the table. I was
trying to get it to work with just two queries before adding the rest. Is
this even a possible? Or am I dreaming?
Click to expand...
Click to expand...
 
well there are two types of union
union all

and

union

union all give you an entire link even with duplicates where union
opnly gives you distinct records

union only makes the union for records not columns when you look at
the data id you have 100 records per union and 16 union all commands
you will get 1600 records not 1008 fields

hope this helps

Regards
Kelvan
 
Jerry, how do you modify this using criteria? For example my union statement
is as so...
INSERT INTO tblPrerequisites ( PrTblID, PrRelatedID )
SELECT linked_Prerequisites.PrereqID, linked_Prerequisites.RelatedID
FROM linked_Prerequisites
WHERE (((linked_Prerequisites.PrereqDesc) Like "*is *"));
UNION SELECT linked_Prerequisites.RelatedID, linked_Prerequisites.PrereqID
FROM linked_Prerequisites
WHERE (((linked_Prerequisites.PrereqDesc) Like "*has*"));

same thing as with ajt, the union part of the query works fine when not
accompanied by the INSERT INTO but when I attempt to run the union as an
append it doesn't work. Any suggestions?...

--------------
Jerry Whittle said:
Try this:

INSERT INTO [APPROP TEST]
SELECT *
FROM (SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107]);
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


vtj said:
I am trying to get a union query to append records to a table. The fields
are identical and in the same order in the table I'm appending to as the
query. I get an error 'Syntax error in from clause' when I try to run this.

The SQL is: INSERT INTO [APPROP TEST] SELECT *
FROM [B04 073107]
UNION ALL
SELECT * FROM [B11 073107];

The two queries work in the union query if I take the 'INSERT INTO [APPROP
TEST]' away from the above SQL. The table is blank to start with and there
are about 40 queries that I would like to union to make the table. I was
trying to get it to work with just two queries before adding the rest. Is
this even a possible? Or am I dreaming?
Click to expand...
Click to expand...
 
Back
Top