Append Query Problem

I'm testing this query and it does not append any new rows from the
source tables unless the destination table is empty. I want to design
the query just to add new records from the destination tables and leave
existing ones in tact. Help appreciated. Query:

INSERT INTO tMASmembers ( Division, CustomerNumber, CustomerName,
AddressLine1, SalesPersonCode, SIC_Code, SIC_Desc )
SELECT AR1_CustomerMaster.Division, AR1_CustomerMaster.CustomerNumber,
AR1_CustomerMaster.CustomerName, AR1_CustomerMaster.AddressLine1,
AR1_CustomerMaster.SalesPersonCode, AR_90_UDF_AR_Customer.Sic_Code,
AR_90_UDF_AR_Customer.Sic_Desc
FROM AR1_CustomerMaster INNER JOIN AR_90_UDF_AR_Customer ON
(AR1_CustomerMaster.Division = AR_90_UDF_AR_Customer.Division) AND
(AR1_CustomerMaster.CustomerNumber =
AR_90_UDF_AR_Customer.CustomerNumber)
WHERE (((AR1_CustomerMaster.Division) Not In (Select
[tMASmembers].[Division] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerNumber) Not In (Select
[tMASmembers].[CustomerNumber] From [tMASmembers])) AND
((AR1_CustomerMaster.CustomerName) Not In (Select
[tMASmembers].[CustomerName] From [tMASmembers])) AND
((AR1_CustomerMaster.AddressLine1) Not In (Select
[tMASmembers].[AddressLine1] From [tMASmembers])) AND
((AR1_CustomerMaster.SalesPersonCode) Not In (Select
[tMASmembers].[SalesPersonCode] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Code) Not In (Select
[tMASmembers].[SIC_Code] From [tMASmembers])) AND
((AR_90_UDF_AR_Customer.Sic_Desc) Not In (Select
[tMASmembers].[SIC_Desc] From [tMASmembers])));

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!One more note: In a test I did, it seems I can only append when I delete
the records from the destination table that all belong to an entire
division (01 or 02). So if I delete all the customers from div 01, I can
append them back. The division and customer number fields are the
primary keys and they come in as text from the ODBC import.

Help appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"Frank Py" <fpy@.proactnet.com> wrote in message
news:3ffd9cd7$0$70302$75868355@.news.frii.net...
> One more note: In a test I did, it seems I can only append when I delete
> the records from the destination table that all belong to an entire
> division (01 or 02). So if I delete all the customers from div 01, I can
> append them back. The division and customer number fields are the
> primary keys and they come in as text from the ODBC import.
> Help appreciated.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

You probably want something like this:

insert into tMASmembers (...)
select (...)
from
AR1_CustomerMaster CM inner join AR_90_UDF_AR_Customer C
on CM.Division = C.Division and CM.CustomerNumber = C.CustomerNumber
where not exists
(select * from tMASmembers tm
where tm.Division = CM.Division and
tm.CustomerNumber = CM.CustomerNumber)

Simon|||Thanks for the example.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!