OK, that is simple enough and I will do that, but I still have a question, because I think (but not sure) that I have done this before and got the error message but did NOT get any duplicate records created.
Are you saying "the correct way to do this and avoid any errors", do you mean error messages or actual errors?
And am I correct that I cannot do this from the query design window and that I must paste the SQL into the SQL window?
---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
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;
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?
__._,_.___
Posted by: david.pratt@outlook.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar