Append: Duplicate Output Destination error

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I had a table with a plot numbers field and compass direction field, along
with other fields. Each plot number could be repeated up to 8 times, as we
used the 8 directions of the compass rose. Query1: I querried out records
based on compass direction. Query2: I then created 2 new fields from
expressions; I copied and pasted the same sql statement for all directions in
query2 and only changed the multiplier for the different direction. All
tables created in query 2 are identical.

When I tried to combine the new values into a table using Append(Insert
Into), I get a Duplicate Output Destination in 'plot number'. Is this because
the plot number is repeated up to 8 times? If not, what is wrong with my
query.

INSERT INTO NeighbourTreeLocations
SELECT [EastTreeN&E].*, [SETreeN&E].*, [SouthTreeN&E].*, [SWTreeN&E].*,
WestTrees.*, [NWTreeN&E].*
FROM [EastTreeN&E], [SETreeN&E], [SouthTreeN&E], [SWTreeN&E], WestTrees,
[NWTreeN&E], [NETreeE&N];
 
SELECT [EastTreeN&E].*, [SETreeN&E].*, [SouthTreeN&E].*, [SWTreeN&E].*,
WestTrees.*, [NWTreeN&E].*
The way I see it your SELECT statement is pulling records from multiple
tables that have multipl fields. You are then trying to INSERT INTO
NeighbourTreeLocations - a single table.
What fields do you have in NeighbourTreeLocations?
 
I had a table with a plot numbers field and compass direction field, along
with other fields. Each plot number could be repeated up to 8 times, as we
used the 8 directions of the compass rose. Query1: I querried out records
based on compass direction. Query2: I then created 2 new fields from
expressions; I copied and pasted the same sql statement for all directions in
query2 and only changed the multiplier for the different direction. All
tables created in query 2 are identical.

When I tried to combine the new values into a table using Append(Insert
Into), I get a Duplicate Output Destination in 'plot number'. Is this because
the plot number is repeated up to 8 times? If not, what is wrong with my
query.

INSERT INTO NeighbourTreeLocations
SELECT [EastTreeN&E].*, [SETreeN&E].*, [SouthTreeN&E].*, [SWTreeN&E].*,
WestTrees.*, [NWTreeN&E].*
FROM [EastTreeN&E], [SETreeN&E], [SouthTreeN&E], [SWTreeN&E], WestTrees,
[NWTreeN&E], [NETreeE&N];
Click to expand...

If these eight tables all have the same structure, I think you need an append
query based on a UNION query:

First go into the SQL window and edit a query:

SELECT * FROM [EastTreeN&E]
UNION ALL
SELECT * FROM [SETreeN&E]
UNION ALL
SELECT * FROM [SouthTreeN&E]
UNION ALL
SELECT * FROM [SWTreeN&E]
UNION ALL
SELECT * FROM WestTrees
UNION ALL
SELECT * FROM [NWTreeN&E]

Save this query as uniAllTrees.
Then create a second query

INSERT INTO NeighborTreeLocations
SELECT * FROM uniAllTrees
 
Thanks John, your solution worked. As I am learning, I want to understand why
my INSERT INTO query didn't work where your UNION did? If you have the time
please reply. Thanks again.
--
Paul


John W. Vinson said:
I had a table with a plot numbers field and compass direction field, along
with other fields. Each plot number could be repeated up to 8 times, as we
used the 8 directions of the compass rose. Query1: I querried out records
based on compass direction. Query2: I then created 2 new fields from
expressions; I copied and pasted the same sql statement for all directions in
query2 and only changed the multiplier for the different direction. All
tables created in query 2 are identical.

When I tried to combine the new values into a table using Append(Insert
Into), I get a Duplicate Output Destination in 'plot number'. Is this because
the plot number is repeated up to 8 times? If not, what is wrong with my
query.

INSERT INTO NeighbourTreeLocations
SELECT [EastTreeN&E].*, [SETreeN&E].*, [SouthTreeN&E].*, [SWTreeN&E].*,
WestTrees.*, [NWTreeN&E].*
FROM [EastTreeN&E], [SETreeN&E], [SouthTreeN&E], [SWTreeN&E], WestTrees,
[NWTreeN&E], [NETreeE&N];
Click to expand...

If these eight tables all have the same structure, I think you need an append
query based on a UNION query:

First go into the SQL window and edit a query:

SELECT * FROM [EastTreeN&E]
UNION ALL
SELECT * FROM [SETreeN&E]
UNION ALL
SELECT * FROM [SouthTreeN&E]
UNION ALL
SELECT * FROM [SWTreeN&E]
UNION ALL
SELECT * FROM WestTrees
UNION ALL
SELECT * FROM [NWTreeN&E]

Save this query as uniAllTrees.
Then create a second query

INSERT INTO NeighborTreeLocations
SELECT * FROM uniAllTrees
Click to expand...
 
Thanks John, your solution worked. As I am learning, I want to understand why
my INSERT INTO query didn't work where your UNION did? If you have the time
please reply. Thanks again.
Click to expand...

Your query (attempted to) join the seven tables "side by side": if your table
had ten fields, you would get a result with 70 fields. Since you had no joins
between the tables, you would get *every possible combination* of records from
the seven tables - if you had 10 records in the first table, 20 in the second,
30 in the third etc. you would have 10*20*30*40*50*60*70 = 50400000000 records
in the result. This is called a "Cartesian join" and can occasionally be
useful, but generally it's a mistake!

The UNION query joins tables "end to end" instead: using my example, you would
have a recordset with ten fields and 10 + 20 + 30... records.

Try creating a query with two of your tree tables, a stripped down version of
your query. Just add the two tables, no join line. Open the query datasheet
and see what you get. That's how queries are designed to work - you do need to
get the logic clear before just diving in!
 
Back
Top