Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Append query

Posted on 2009-05-18
12
Medium Priority
?
262 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

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…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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