If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Criteria in append querie
I want to append only non existing ClientID fields into the
destination table. Client ID is not a primairy field because it is a import file from website(can't put it in primairy key because import program refresh the table every 5 minutes and replaces all data.) So i do a append query with this criteria: =not Exists (SELECT 'KlantID' from [Klant_copy] where [Klant_copy].KlantID = [Klant].KlantID) Klant= to be copied table (KlantID fields) Klant_copy= destination table (also KlantID fields) Can someone tell me why my append querie criteria does'nt work (to copy only non existing KlantID field in the destination table). Thanx in advance! |
#2
|
|||
|
|||
Criteria in append querie
On Sat, 6 Mar 2010 04:13:00 -0800 (PST), Bart wrote:
The "=" at the beginning of your where-clause does not belong. If you switch to sql view you should see: .... where not exists(...) -Tom. Microsoft Access MVP I want to append only non existing ClientID fields into the destination table. Client ID is not a primairy field because it is a import file from website(can't put it in primairy key because import program refresh the table every 5 minutes and replaces all data.) So i do a append query with this criteria: =not Exists (SELECT 'KlantID' from [Klant_copy] where [Klant_copy].KlantID = [Klant].KlantID) Klant= to be copied table (KlantID fields) Klant_copy= destination table (also KlantID fields) Can someone tell me why my append querie criteria does'nt work (to copy only non existing KlantID field in the destination table). Thanx in advance! |
#3
|
|||
|
|||
Criteria in append querie
On 6 mrt, 16:04, Tom van Stiphout wrote:
On Sat, 6 Mar 2010 04:13:00 -0800 (PST), Bart wrote: The "=" at the beginning of your where-clause does not belong. If you switch to sql view you should see: ... where not exists(...) -Tom. Microsoft Access MVP I want to append only non existing ClientID fields into the destination table. Client ID is not a primairy field because it is a import file from website(can't put it in primairy key because import program refresh the table every 5 minutes and replaces all data.) So i do a append query with this criteria: =not Exists (SELECT 'KlantID' from [Klant_copy] where [Klant_copy].KlantID = [Klant].KlantID) Klant= to be copied table (KlantID fields) Klant_copy= destination table (also KlantID fields) Can someone tell me why my append querie criteria does'nt work (to copy only non existing KlantID field in the destination table). Thanx in advance!- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hello Tom, Thank you, for your advice but i don't have the results wanted: What is wrong with this sql code? Why it does'nt copy the difference(update the second table with it) between the first and second table? INSERT INTO DestinationTable (OrderID, name) SELECT SourceTable.OrderID, name FROM SourceTable INNER JOIN DestinationTable ON SourceTable.orderID,name = DestinationTable.orderID, name WHERE DestinationTable.orderID Is Null Thanx in advance ps. remember there are na primairy keys... |
#4
|
|||
|
|||
Criteria in append querie
On 7 mrt, 07:48, Bart wrote:
On 6 mrt, 16:04, Tom van Stiphout wrote: On Sat, 6 Mar 2010 04:13:00 -0800 (PST), Bart wrote: The "=" at the beginning of your where-clause does not belong. If you switch to sql view you should see: ... where not exists(...) -Tom. Microsoft Access MVP I want to append only non existing ClientID fields into the destination table. Client ID is not a primairy field because it is a import file from website(can't put it in primairy key because import program refresh the table every 5 minutes and replaces all data.) So i do a append query with this criteria: =not Exists (SELECT 'KlantID' from [Klant_copy] where [Klant_copy].KlantID = [Klant].KlantID) Klant= to be copied table (KlantID fields) Klant_copy= destination table (also KlantID fields) Can someone tell me why my append querie criteria does'nt work (to copy only non existing KlantID field in the destination table). Thanx in advance!- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hello Tom, Thank you, for your advice but i don't have the results wanted: What is wrong with this sql code? Why it does'nt copy the difference(update the second table with it) between the first and second table? INSERT INTO DestinationTable (OrderID, name) SELECT SourceTable.OrderID, name FROM SourceTable INNER JOIN DestinationTable * * * * ON SourceTable.orderID,name = DestinationTable.orderID, name WHERE DestinationTable.orderID Is Null Thanx in advance ps. remember there are na primairy keys...- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - i found it! SELECT Klant.KlantID, Klant.Instellingsnaam, Klant.Naam, Klant.Voornaam, Klant.EmailAdres, Klant.Directe_telefoon, Klant.KlantRootID, Klant.Memo, Klant.SoortKlant, Klant.Straatnaam_nr, Klant.Gemeente, Klant.Postcode, Klant.Passwoord FROM Klant LEFT JOIN Klant_copy ON Klant.KlantID = Klant_copy.KlantID WHERE (((Klant_copy.KlantID) Is Null)); INSERT INTO Klant_copy SELECT Query98.* FROM Query98; |
Thread Tools | |
Display Modes | |
|
|