Kamis, 02 Februari 2017

Re: [MS_AccessPros] Append Query Does not work

 

I don't think you need an updatable query as the source of an append query or maketable query. 


Regards,

Duane



From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, February 2, 2017 8:57 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 
http://www.viescas.com/
www.viescas.com
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 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:

INSERT INTO lu_tblProductFamilies ( ProductFamilyName )
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 
http://www.viescas.com/
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 
http://www.viescas.com/ 
(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?  














__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (12)
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.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)