INSERT INTO lu_tblProductFamilies ( ProductFamilyName )
SELECT [_tblImportTanks2].ProductFamily
FROM _tblImportTanks2
WHERE _tblImportTanks2.ProductFamily NOT IN
(SELECT lu_tblProductFamilies.ProductFamilyName FROM tblProductFamilies as lu_tblProductFamilies);
Darrell
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, February 02, 2017 8:44 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Append Query Does not work
I am getting a syntax error message saying error in:
_tblImportTanks2.ProductFamily NOT IN
(SELECT lu_tblProductFamilies.ProductFamilyName);
I added brackets around _tblImportTanks2 and tried it, to match what you had done with the SELECT statement and it did not help.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
David-
The correct way to do this that avoids any insert errors is:
INSERT INTO lu_tblProductFamilies ( ProductFamilyName )
SELECT [_tblImportTanks2].ProductFamily
FROM _tblImportTanks2
WHERE _tblImportTanks2.ProductFamily NOT IN
(SELECT lu_tblProductFamilies.ProductFamilyName);
You can test it by just running the SELECT part of the query to see if you get any rows returned.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Feb 2, 2017, at 5:37 AM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have imported a table from an existing spreadsheet. I am just trying to use the records in the table to append new rows to an existing table, tblProductFamilies. The table created from Excel has a field named ProductFamily. there are many duplicate values in this field and also some of the field values are already in the tblProductFamilies. I am just trying to get an append query to append new records to the tblProductFamilies where the ProductFamily in the imported table does not exist in the tblProductFamilies.
The table imported from Excel is named _tblImportTanks2. The only field I need from that table at the moment is named ProductFamily. It is short text.
I want to append to tblProductFamilies and it has fields:
ProductFamily ID; primary key; autonumber
ProductFamilyName: short text; indexed and unique
Description; short text
I am using query design view and the SQL being generated is:
INSERT INTO lu_tblProductFamilies ( ProductFamilyName )
SELECT [_tblImportTanks2].ProductFamily
FROM _tblImportTanks2;
The query does not add any new records at all, "due to key violations". I was expecting there to be about 15 new records created which did not previously exist in tblProductFamilies, and the rest (about 160 records) to be rejected due to key violations. Instead, all are being rejected.
Can you help me with what I am doing wrong?
The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.
Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.
Posted by: "Embrey, Darrell" <DEmbrey@bcbsm.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar