Append Query - Problem

G

grantschneider

I am trying to create a simple append query with the following SQL
code:

INSERT INTO [Lead Source Analysis] ( Account, Code, Owner, Field5,
Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month, [Master
Sales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team],
*
FROM [Lead Source Analysis], [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND (([Master
Sales Forecast].Month)="JUN"));

For some reason I keep getting the error: Duplicate Output
Destination: "Code". If I delete code or move it over so it isnt the
first column it says the same thing about Accounts and so on.
 
A

Allen Browne

What's with the wildcard in the last field place (i.e. immediately before
the FROM clause)?
 
G

grantschneider

What's with the wildcard in the last field place (i.e. immediately before
the FROM clause)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.




I am trying to create a simple append query with the following SQL
code:
Click to expand...
INSERT INTO [Lead Source Analysis] ( Account, Code, Owner, Field5,
Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month, [Master
Sales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team],
*
FROM [Lead Source Analysis], [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND (([Master
Sales Forecast].Month)="JUN"));
Click to expand...
For some reason I keep getting the error: Duplicate Output
Destination: "Code". If I delete code or move it over so it isnt the
first column it says the same thing about Accounts and so on.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Not sure, on access it shows up at the end of the line above it. If i
take it out it says I have SYNTAX ERROR in my INSERT INTO statement
 
G

grantschneider

I am trying to create a simple append query with the following SQL
code:
Click to expand...
INSERT INTO [Lead Source Analysis] ( Account, Code, Owner, Field5,
Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month, [Master
Sales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team],
*
FROM [Lead Source Analysis], [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND (([Master
Sales Forecast].Month)="JUN"));
Click to expand...
For some reason I keep getting the error: Duplicate Output
Destination: "Code". If I delete code or move it over so it isnt the
first column it says the same thing about Accounts and so on.
Click to expand...

Grant Schneider,

Here's your query straightened up a bit:

INSERT INTO [Lead Source Analysis]
(Account
,Code
,Owner
,Field5
,Aircraft
,Share
,Country
,Region
,VP
,Team)
SELECT [Master Sales Forecast].Account
,[Master Sales Forecast].Code
,[Master Sales Forecast].Owner
,[Master Sales Forecast].Month
,[Master Sales Forecast].Aircraft
,[Master Sales Forecast].[Share Total]
,[Master Sales Forecast].Country
,[Master Sales Forecast].Territory
,[Master Sales Forecast].VP
,[Master Sales Forecast].[Territory Team]
,*
FROM [Lead Source Analysis]
,[Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account) = "New Sale")
AND (([Master Sales Forecast].Month) = "JUN"));

You have a * at the end of the SELECT clause.

I am willing to be that is causing a problem.

I also notice that you have what appears to be a cartesian join. That may also cause a
problem (too many rows returned).

Sincerely,

Chris O.- Hide quoted text -

- Show quoted text -
Click to expand...

how do i fix the cartesian joint problem? I noticed I had endless
duplicate copies and wanted to fix that as well.

also, when i take out the wildcard I get a syntax error: INSERT INTO
statement

thank you all for the help
Grant
 
G

grantschneider

I am trying to create a simple append query with the following SQL
code:
Click to expand...
INSERT INTO [Lead Source Analysis] ( Account, Code, Owner, Field5,
Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month, [Master
Sales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team],
*
FROM [Lead Source Analysis], [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND (([Master
Sales Forecast].Month)="JUN"));
Click to expand...
For some reason I keep getting the error: Duplicate Output
Destination: "Code". If I delete code or move it over so it isnt the
first column it says the same thing about Accounts and so on.
Click to expand...

Grant Schneider,

Here's your query straightened up a bit:

INSERT INTO [Lead Source Analysis]
(Account
,Code
,Owner
,Field5
,Aircraft
,Share
,Country
,Region
,VP
,Team)
SELECT [Master Sales Forecast].Account
,[Master Sales Forecast].Code
,[Master Sales Forecast].Owner
,[Master Sales Forecast].Month
,[Master Sales Forecast].Aircraft
,[Master Sales Forecast].[Share Total]
,[Master Sales Forecast].Country
,[Master Sales Forecast].Territory
,[Master Sales Forecast].VP
,[Master Sales Forecast].[Territory Team]
,*
FROM [Lead Source Analysis]
,[Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account) = "New Sale")
AND (([Master Sales Forecast].Month) = "JUN"));

You have a * at the end of the SELECT clause.

I am willing to be that is causing a problem.

I also notice that you have what appears to be a cartesian join. That may also cause a
problem (too many rows returned).

Sincerely,

Chris O.- Hide quoted text -

- Show quoted text -
Click to expand...

as well, i copied your SQL and I get the same error.
 
J

John Spencer

REMOVE the * (all fields) in your SQL.

AND remove [Lead Source Analysis] from the SELECT query's FROM clause unless
you want multiple records inserted into the Lead Source Analysis table.

INSERT INTO [Lead Source Analysis]
( Account, Code, Owner, Field5,Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month
, [MasterSales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team]
FROM [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND
(([Master Sales Forecast].Month)="JUN"));

What I don't understand is why you are doing this. You should be able to
work with the data in the SELECT query as if it were a table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
C

Chris2

I am trying to create a simple append query with the following SQL
code:

INSERT INTO [Lead Source Analysis] ( Account, Code, Owner, Field5,
Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month, [Master
Sales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team],
*
FROM [Lead Source Analysis], [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND (([Master
Sales Forecast].Month)="JUN"));

For some reason I keep getting the error: Duplicate Output
Destination: "Code". If I delete code or move it over so it isnt the
first column it says the same thing about Accounts and so on.
Click to expand...

Grant Schneider,

Here's your query straightened up a bit:

INSERT INTO [Lead Source Analysis]
(Account
,Code
,Owner
,Field5
,Aircraft
,Share
,Country
,Region
,VP
,Team)
SELECT [Master Sales Forecast].Account
,[Master Sales Forecast].Code
,[Master Sales Forecast].Owner
,[Master Sales Forecast].Month
,[Master Sales Forecast].Aircraft
,[Master Sales Forecast].[Share Total]
,[Master Sales Forecast].Country
,[Master Sales Forecast].Territory
,[Master Sales Forecast].VP
,[Master Sales Forecast].[Territory Team]
,*
FROM [Lead Source Analysis]
,[Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account) = "New Sale")
AND (([Master Sales Forecast].Month) = "JUN"));


You have a * at the end of the SELECT clause.

I am willing to be that is causing a problem.


I also notice that you have what appears to be a cartesian join. That may also cause a
problem (too many rows returned).


Sincerely,

Chris O.
 
C

Chris2

I am trying to create a simple append query with the following SQL
code:
Click to expand...
INSERT INTO [Lead Source Analysis] ( Account, Code, Owner, Field5,
Aircraft, Share, Country, Region, VP, Team )
SELECT [Master Sales Forecast].Account, [Master Sales Forecast].Code,
[Master Sales Forecast].Owner, [Master Sales Forecast].Month, [Master
Sales Forecast].Aircraft, [Master Sales Forecast].[Share Total],
[Master Sales Forecast].Country, [Master Sales Forecast].Territory,
[Master Sales Forecast].VP, [Master Sales Forecast].[Territory Team],
*
FROM [Lead Source Analysis], [Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account)="New Sale") AND (([Master
Sales Forecast].Month)="JUN"));
Click to expand...
For some reason I keep getting the error: Duplicate Output
Destination: "Code". If I delete code or move it over so it isnt the
first column it says the same thing about Accounts and so on.
Click to expand...

Grant Schneider,

Here's your query straightened up a bit:

INSERT INTO [Lead Source Analysis]
(Account
,Code
,Owner
,Field5
,Aircraft
,Share
,Country
,Region
,VP
,Team)
SELECT [Master Sales Forecast].Account
,[Master Sales Forecast].Code
,[Master Sales Forecast].Owner
,[Master Sales Forecast].Month
,[Master Sales Forecast].Aircraft
,[Master Sales Forecast].[Share Total]
,[Master Sales Forecast].Country
,[Master Sales Forecast].Territory
,[Master Sales Forecast].VP
,[Master Sales Forecast].[Territory Team]
,*
FROM [Lead Source Analysis]
,[Master Sales Forecast]
WHERE ((([Master Sales Forecast].Account) = "New Sale")
AND (([Master Sales Forecast].Month) = "JUN"));

You have a * at the end of the SELECT clause.

I am willing to be that is causing a problem.

I also notice that you have what appears to be a cartesian join. That may also cause a
problem (too many rows returned).

Sincerely,

Chris O.- Hide quoted text -

- Show quoted text -
Click to expand...

as well, i copied your SQL and I get the same error.
Click to expand...

Grant Schneider,

I did not provide any SQL. The above was your SQL, aligned for readability.

On a *copy* of your database, try:

INSERT INTO [Lead Source Analysis]
(Account
,Code
,Owner
,Field5
,Aircraft
,Share
,Country
,Region
,VP
,Team)
SELECT [Master Sales Forecast].Account
,[Master Sales Forecast].Code
,[Master Sales Forecast].Owner
,[Master Sales Forecast].Month
,[Master Sales Forecast].Aircraft
,[Master Sales Forecast].[Share Total]
,[Master Sales Forecast].Country
,[Master Sales Forecast].Territory
,[Master Sales Forecast].VP
,[Master Sales Forecast].[Territory Team]
FROM [Lead Source Analysis]
INNER JOIN
[Master Sales Forecast]
ON [Lead Source Analysis].Account = [Master Sales Forecast].Account
WHERE ((([Master Sales Forecast].Account) = "New Sale")
AND (([Master Sales Forecast].Month) = "JUN"));

Notes:

I am making a huge guess on the column named after the ON keyword to use for the join. If
my guess about the column is wrong, the above will be wrong as well. You will need to
make the correct decision about what column or columns to use. In a post on another
branch of this thread, John Spencer speculates that you should remove [Lead Source
Analysis] entirely from the FROM clause.

When including a * by itself in a column list, it automatically generates an output column
for every column in every table named on the FROM clause, in addition to any columns that
are already named.


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running sums 12
problem with forms and queries 2
Form not working with query 6
Problem with forms/parameter query 2
form not working with query 0
Finding matching records on a form 0
Query from unrelated tables help please 4
Query Totals 3

Top