Need help with Insert unmatched query 1

[DougP]

I want to find any project not in one table and add it to a third table. Then allow the user to check a box next to the ones to import. The following SQL gives an error "Duplicate output Destination Project Number”. If I remove Project Number from the left of the QBE grid then it does not insert the Project Number but leaves it blank. No good. I need the have the project number later.

INSERT INTO ChooseTheseToImport ( [Project Number], Service, [Number], [Building Name], [Date], [Project Number] )
SELECT [ChainOfCustody Old].[Project Number], [ChainOfCustody Old].Service, [ChainOfCustody Old].Number, [ChainOfCustody Old].[Building Name], [ChainOfCustody Old].Date, ChainOfCustody.[Project Number]
FROM [ChainOfCustody Old] LEFT JOIN ChainOfCustody ON [ChainOfCustody Old].[Project Number] = ChainOfCustody.[Project Number]
WHERE (((ChainOfCustody.[Project Number]) Is Null));

DougP

 

[GingerR]

You are trying to insert two diff things into the same field ProjectNumber - can't do that.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[DougP]

No just trying to insert the project number.
When I use the QBE grid that's what it creates. its an unmatched query so it add the [Project Number]) Is Null) column. It work fine if I run it without the insert line.

DougP

 

[GingerR]

You have to take the 2nd "PROJECT NUMBER" out (Yes, Access throws it in there because it's trying to help, but you MUST take it out - the one in the APPEND TO row on the far Right above the criteria IS NULL.

INSERT INTO ChooseTheseToImport ( [Project Number], Service, [Number], [Building Name], [Date] )
SELECT [ChainOfCustody Old].[Project Number], [ChainOfCustody Old].Service, [ChainOfCustody Old].Number, [ChainOfCustody Old].[Building Name], [ChainOfCustody Old].Date
FROM [ChainOfCustody Old] LEFT JOIN ChainOfCustody ON [ChainOfCustody Old].[Project Number] = ChainOfCustody.[Project Number]
WHERE (((ChainOfCustody.[Project Number]) Is Null));

I built your tables and fixed this and it works fine.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[DougP]

OMG it works!
Thank you so much!!!
have a star
_/\_
\ /
/ \


DougP