I'm sql challenged, so I rely on the design grid and create an unmatched query, and then use that query to delete the append query or just do the unmatched logic in my append.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, February 02, 2017 7:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Append Query Does not work
Duane-
Yup, that can be more efficient, but I think you'll get "must use an updatable query" if there isn't a unique key on ProductFamilyName.
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 3:38 PM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I generally use this syntax which is usually a little better performing:
SELECT DISTINCT [_tblImportTanks2].ProductFamily
FROM _tblImportTanks2 LEFT JOIN
lu_tblProductFamilies ON [_tblImportTanks2].ProductFamily = lu_tblProductFamilies.ProductFamilyName
WHERE lu_tblProductFamilies.ProductFamilyName is Null
Regards,
Duane Hookom
Vevey, Switzerland
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 2, 2017 8:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Append Query Does not work
OK, thank you both. I have to run to work now and will get back to this in the evening.
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Try doing it on the query grid. Start a query on the imported table. Add ProductFamily to the field grid. Change to an Append (INSERT) query and select the target table. Then try to hand enter on the criteria line under ProductFamily:
NOT IN (SELECT ProductFamilyName FROM lu_tblProductFamilies)
Sorry, but I forgot the stupid FROM clause in the subquery!
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
| | Welcome to the home of John Viescas Consulting. If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book ... |
(Paris, France)
On Feb 2, 2017, at 2:44 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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?
This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
Posted by: Liz Ravenwood <Liz_Ravenwood@beaerospace.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
Tidak ada komentar:
Posting Komentar