Append query

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
ba_trainerAsked:
Who is Participating?
 
Joeyen5Connect With a Mentor Commented:
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
 
ba_trainerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
ba_trainerAuthor Commented:
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
 
Joeyen5Commented:
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
 
ba_trainerAuthor Commented:
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
 
Joeyen5Commented:
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
 
ba_trainerAuthor Commented:
Could we use * for all?
0
 
ba_trainerAuthor Commented:
I changed the field name to TTY, but it gave me the same error for the next field [EMP NOTES]
0
 
Joeyen5Commented:
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
 
ba_trainerAuthor Commented:
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
 
ba_trainerAuthor Commented:
Thanks for sticking with it!
I really appreciate it!
Beth
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.