Criteria in append querie

[Bart]

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!

 

[Tom van Stiphout]

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

 

[Bart]

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...

 

[Bart]

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;