Solved

Append query

Posted on 2009-05-18
12
254 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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
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
 
LVL 2

Expert Comment

by:Joeyen5
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:ba_trainer
Comment Utility
Could we use * for all?
0
 

Author Comment

by:ba_trainer
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for sticking with it!
I really appreciate it!
Beth
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'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 …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now