Solved

Append query

Posted on 2009-05-18
12
257 Views
Last Modified: 2012-05-07
I have TABLE 1 - "Call cycle history".

I need an append query that will add all items from
TABLE 2 - "Accounts on update but not in current call cycle"
to TABLE 1.

Question:
Can I only add the new items but overwrite items that already exist?
Thanks!
Beth
0
Comment
Question by:ba_trainer
  • 7
  • 4
12 Comments
 

Author Comment

by:ba_trainer
ID: 24415320
Sorry - I forgot to attach SQL.
INSERT INTO [Call cycle history] ( [Territory #], [Emp Notes], [Entity ID], [Facility ID], [FXSAcct], [CC Band], [FXSName], [QOQ1], [QOQ2], [LASTCALLED], [LASTSCHEDULED], [NEXTCALL], [SOURCE], [Classify], [Total Spend], [Zip] )
SELECT [Accounts on update but not in current call cycle].[Territory #], [Accounts on update but not in current call cycle].[Emp Notes], [Accounts on update but not in current call cycle].[Entity ID], [Accounts on update but not in current call cycle].[Facility ID], [Accounts on update but not in current call cycle].[FXSAcct], [Accounts on update but not in current call cycle].[CC Band], [Accounts on update but not in current call cycle].[FXSName], [Accounts on update but not in current call cycle].[QOQ1], [Accounts on update but not in current call cycle].[QOQ2], [Accounts on update but not in current call cycle].[LASTCALLED], [Accounts on update but not in current call cycle].[LASTSCHEDULED], [Accounts on update but not in current call cycle].[NEXTCALL], [Accounts on update but not in current call cycle].[SOURCE], [Accounts on update but not in current call cycle].[Classify], [Accounts on update but not in current call cycle].[Total Spend], [Accounts on update but not in current call cycle].[Zip]
FROM [Accounts on update but not in current call cycle];

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24419282
>Can I only add the new items but overwrite items that already exist?
yes, but that will be 2 queries.

1 UPDATE for the existing rows, and 1 INSERT for the non-existing rows.
for part 1, you need to update with a join, for part 2 you need to create a "non-matched join".

for which part do you need more help?
0
 

Author Comment

by:ba_trainer
ID: 24420980
Ok, thanks!
Can you help me update with a non-matched join?
I need to add all fields from the 'Accounts on update but not in current call cycle' TABLE
to the 'Call cycle history' TABLE where the [Entitity ID] do not exist.

Beth
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 2

Expert Comment

by:Joeyen5
ID: 24421032
Just add a where clause to pick on new Entity ID's.  See code below
INSERT INTO [Call cycle history] ( [Territory #], [Emp Notes], [Entity ID], [Facility ID], [FXSAcct], [CC Band], [FXSName], [QOQ1], [QOQ2], [LASTCALLED], [LASTSCHEDULED], [NEXTCALL], [SOURCE], [Classify], [Total Spend], [Zip] )
SELECT [Accounts on update but not in current call cycle].[Territory #], [Accounts on update but not in current call cycle].[Emp Notes], [Accounts on update but not in current call cycle].[Entity ID], [Accounts on update but not in current call cycle].[Facility ID], [Accounts on update but not in current call cycle].[FXSAcct], [Accounts on update but not in current call cycle].[CC Band], [Accounts on update but not in current call cycle].[FXSName], [Accounts on update but not in current call cycle].[QOQ1], [Accounts on update but not in current call cycle].[QOQ2], [Accounts on update but not in current call cycle].[LASTCALLED], [Accounts on update but not in current call cycle].[LASTSCHEDULED], [Accounts on update but not in current call cycle].[NEXTCALL], [Accounts on update but not in current call cycle].[SOURCE], [Accounts on update but not in current call cycle].[Classify], [Accounts on update but not in current call cycle].[Total Spend], [Accounts on update but not in current call cycle].[Zip]
FROM [Accounts on update but not in current call cycle] 
Where [Accounts on update but not in current call cycle].[Entity ID] not in (Select Distinct [Entity ID] from [Call cycle history]);

Open in new window

0
 

Author Comment

by:ba_trainer
ID: 24421349
I am getting an error message that [Accounts on update but not in current call cycle].[Territory #] could refer to more than one table in my FROM clause?
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 24421427
That's strange. I think the query should work.  

One issue may be that the [Territory #] has a # in the title.  The # is a wild card character in Access where # represents a number.
0
 

Author Comment

by:ba_trainer
ID: 24422696
Could we use * for all?
0
 

Author Comment

by:ba_trainer
ID: 24422745
I changed the field name to TTY, but it gave me the same error for the next field [EMP NOTES]
0
 
LVL 2

Expert Comment

by:Joeyen5
ID: 24422903
You could probably use Select * if [Accounts on update but not in current call cycle] has all the columns listed at the Insert Into line.  (No more fields, no less fields.).

However, I think your best bet is to rename the [Territory #] to something without the # sign.  For the [EMP Notes], it may not like the space.  

For coding purposes, it's probably best not to use spaces. You can name the field EMP_Notes (with an underscore instead of the space.) Then you can display "EMP Notes" when you query if you want.  (Select EMP_Notes as 'EMP Notes' from [Call Cycle History])

Additionally, you should probably change your table names so that they don't have spaces and don't use key words (i.e. 'not in').  It may be good to keep the table names short too.  (Saves on typos.)
0
 

Author Comment

by:ba_trainer
ID: 24423453
I did some updates. This returns a dialog box asking for Call cycle history Entity ID.

Can you help? Thanks! Beth
INSERT INTO [Call cycle history] ( TTY, [Emp Notes], [Entity ID], [Facility ID], FXSAcct, [CC Band], FXSName, QOQ1, QOQ2, LASTCALLED, LASTSCHEDULED, NEXTCALL, SOURCE, Classify, [Total Spend], Zip )
SELECT [Accounts on current call cycle but not on update].[District 3 total list1].TTY, [Accounts on current call cycle but not on update].[District 3 total list1].[Emp Notes], [Accounts on current call cycle but not on update].[District 3 total list1].[Entity ID], [Accounts on current call cycle but not on update].[District 3 total list1].[Facility ID], [Accounts on current call cycle but not on update].[District 3 total list1].FXSAcct, [Accounts on current call cycle but not on update].[District 3 total list1].[CC Band], [Accounts on current call cycle but not on update].[District 3 total list1].FXSName, [Accounts on current call cycle but not on update].[District 3 total list1].QOQ1, [Accounts on current call cycle but not on update].[District 3 total list1].QOQ2, [Accounts on current call cycle but not on update].[District 3 total list1].LASTCALLED, [Accounts on current call cycle but not on update].[District 3 total list1].LASTSCHEDULED, [Accounts on current call cycle but not on update].[District 3 total list1].NEXTCALL, [Accounts on current call cycle but not on update].[District 3 total list1].SOURCE, [Accounts on current call cycle but not on update].[District 3 total list1].Classify, [Accounts on current call cycle but not on update].[District 3 total list1].[Total Spend], [Accounts on current call cycle but not on update].[District 3 total list1].Zip
FROM [Accounts on current call cycle but not on update]
WHERE (([District 3 total list1].[Entity ID] Not In ([Call cycle history].[Entity ID])));

Open in new window

0
 
LVL 2

Accepted Solution

by:
Joeyen5 earned 500 total points
ID: 24424661
I think it is popping up because [Call cycle history] is not in the From clause. In any case, you should be able to fix it by changing the where clause to the following (using a subquery.)

WHERE (([District 3 total list1].[Entity ID] Not In (Select distinct [Entity ID] from [Call cycle history])));
0
 

Author Closing Comment

by:ba_trainer
ID: 31582718
Thanks for sticking with it!
I really appreciate it!
Beth
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question