Append query help

[Cam]

Hello,

What criteria do I put in the append query so that I will append only the
data with the ProdOrd (text field: 000123456) the that are NOT currently on
the appended table named ProdOrderWIP. Thanks

 

[John W. Vinson]

Hello,

What criteria do I put in the append query so that I will append only the
data with the ProdOrd (text field: 000123456) the that are NOT currently on
the appended table named ProdOrderWIP. Thanks

Use an Unmatched Query Wizard to exclude existing ProdOrd records:

INSERT INTO ProdOrderWIP
SELECT <blah blah blah>
FROM sourcetable LEFT JOIN ProdOrderWip
ON sourcetable.ProdOrd = ProdOrderWip.ProdOrd
WHERE ProdOrderWip.ProdOrd IS NULL;

The Left Join will match every record in your source table with the
corresponding record in ProdOrderWIP, if there is one; if there isn't a
corresponding record, the ProdOrderWIP field(s) will be NULL - so the where
operand will cause the *matched* records to be excluded from the append,
leaving only the unmatched ones.

 

[Cam]

John,

I tried your sugguestion and it is not filtering only the records that do
not match any record in the existing table. I guess to clarify here is my
table and query.
Table:
ProdOrderWIP (this is the appended table, where the new data is copy to)
Query:
qryProdOrderWIP (query with the new data feed in everyday)
ProdOrderWIP_Append (Appended query to append new data to the table).

I hope this will clear up on what I am trying to append. THanks

 

[John W. Vinson]

John,

I tried your sugguestion and it is not filtering only the records that do
not match any record in the existing table. I guess to clarify here is my
table and query.
Table:
ProdOrderWIP (this is the appended table, where the new data is copy to)
Query:
qryProdOrderWIP (query with the new data feed in everyday)
ProdOrderWIP_Append (Appended query to append new data to the table).

I hope this will clear up on what I am trying to append. THanks

Not really. There's no such thing as "an appended query". Could you perhaps
post the SQL view of qryProdOrderWIP and ProdOrderWIP_Append?

 

[Cam]

John,

I got it to work. Thanks
I just forgot about the where function the SQL query.