473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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!
Jul 20 '05 #1
3 4116
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!
Jul 20 '05 #2

"Frank Py" <fp*@proactnet.com> wrote in message
news:3f***********************@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
Jul 20 '05 #3
Thanks for the example.

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: PMB | last post by:
Thank you in advance for any and all assistance. I'm trying to use a make table query to pull the last transactionID, so I can use an append query to reset the transactionID to the next...
7
by: | last post by:
I found similiar issues in MS-KB but nothing that helped me; got the Windows and Office updates from the MS website but that hasn't changed the behavior of this problem, and I don't see anything...
1
by: Aaron | last post by:
Hello fellow programmers, I am trying to run an append/update query from code, a command button on a form initiates the queries. the format i am using is; ...
1
by: Kristina | last post by:
I'm trying to run multiple append queries in an Access front end that append data from tables in a .mdb into tables in SQL Server 2000. My Access front end has a link to both tables, the .mdb and...
1
by: David Barger | last post by:
Greetings, It appears that an Append Query I run in Access XP is randomly failing to append a field. I have payroll data being entered into a payroll database. This data is exported daily to...
5
by: Michael C via AccessMonster.com | last post by:
Hello, I have a table that I am appending 3 seperate tables into. My main problem is that each time I append the data, it simply adds to the data already there. That might sound ok, except that...
1
by: James Hallam | last post by:
I have a form with a subform. When there are no entries in the subform, I have an append query which makes a default entry in the subform (for what I am doing there needs to be at least one value...
4
by: pmacdiddie | last post by:
I have an append query that needs to run every time a line item is added to a subform. The append writes to a table that is the source for a pull down box. Problem is that it takes 5 seconds to...
7
by: wade.wall | last post by:
Hi all, I am having a problem appending data to an existing table. I have two tables with identical fields and I want to append the data from one table (T2) to the first (T1). T1 has 136...
4
by: Scott12345 | last post by:
Hi, here is my situation, I have a DB that tracks machine downtime (30 machines) per day. Several users will update this through the day. I created an append query that creates 30 dummy values and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.