Solved

Append query

Posted on 2009-05-18
12
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Rewrite without the NULLIF 4 33
sql update 2 45
Syntax error creating JSON recordset 4 43
Does INTERSECT return opposite from FULL OUTER JOIN? 4 40
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 …

732 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