• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Append Query with prevent duplicate where clause won't append

My append query won't work once I add this where clause. I'm trying to prevent duplicate entries. Any help would be greatly appreciated.

INSERT INTO tblMasterFundEMAIL...

FROM tmpFundEMAIL
WHERE (((tmpFundEMAIL.LoanNum) Not In (Select [LoanNum] from tblMasterFundEMAIL)));
0
chrisamuel
Asked:
chrisamuel
  • 4
  • 3
1 Solution
 
Steve BinkCommented:
INSERT INTO tblMasterFundEMAIL (SELECT * FROM tmpFundEMAIL WHERE tmpFundEMAIL.LoanNum NOT IN (SELECT DISTINCT [LoanNum] FROM tblMasterFundEMAIL))
0
 
chrisamuelAuthor Commented:
Hmm, didn't work. I'm basically taking a table with too many fields and putting selected fields with data types of my choice into another table with this. And it works only if I take out the where clause:

INSERT INTO tblMasterFundEMAIL ( responseid, [HFC Avail], [HFC Advice], [HFC Easy Process], [HFC Overall], [LSII Avail], [LSII Inform], [LSII Easy Close], [LSII Overall], [Close Date], [Close Costs], Recommend, Comments, LoanNum, [Bus Par], Fulfillment_Source, Relo_source, third_party_ref, HFC, [HFC Num], LSII, RAM, TM, [Dt Received], AppDt, PhaseDt, PurposeCd, MailStandardAdd, MailCity, MailState, MailZip, AreaCode, PhoneNum, Email )
SELECT tmpFundEMAIL.responseid, tmpFundEMAIL.q2_1 AS [HFC Avail], tmpFundEMAIL.q2_2 AS [HFC Advice], tmpFundEMAIL.q2_3 AS [HFC Easy Process], tmpFundEMAIL.q2_4 AS [HFC Overall], tmpFundEMAIL.q3_1 AS [LSII Avail], tmpFundEMAIL.q3_2 AS [LSII Inform], tmpFundEMAIL.q3_3 AS [LSII Easy Close], tmpFundEMAIL.q3_4 AS [LSII Overall], tmpFundEMAIL.q4 AS [Close Date], tmpFundEMAIL.q5 AS [Close Costs], tmpFundEMAIL.q6 AS Recommend, tmpFundEMAIL.q7 AS Comments, tmpFundEMAIL.LoanNum, tmpFundEMAIL.ATS_Source AS [Bus Par], tmpFundEMAIL.Fulfillment_Source, tmpFundEMAIL.Relo_source, tmpFundEMAIL.third_party_ref, tmpFundEMAIL.AE_Name AS HFC, tmpFundEMAIL.aenum AS [HFC Num], tmpFundEMAIL.LSII, tmpFundEMAIL.RAM, tmpFundEMAIL.TM, tmpFundEMAIL.date_received AS [Dt Received], tmpFundEMAIL.AppDt, tmpFundEMAIL.PhaseDt, tmpFundEMAIL.PurposeCd, tmpFundEMAIL.MailStandardAdd, tmpFundEMAIL.MailCity, tmpFundEMAIL.MailState, tmpFundEMAIL.MailZip, tmpFundEMAIL.AreaCode, tmpFundEMAIL.PhoneNum, tmpFundEMAIL.BEMail AS Email
FROM tmpFundEMAIL
WHERE (((tmpFundEMAIL.LoanNum) Not In (Select Distinct [LoanNum] from tblMasterFundEMAIL)));
0
 
Steve BinkCommented:
Try using a SELECT..INTO statement, then INSERT from the new table.
0
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!

 
chrisamuelAuthor Commented:
not sure I know what that means
0
 
Steve BinkCommented:
Ok, try this:

1) Create and save this query.  In this example, I'll call it "Query1"
     SELECT a.responseid, a.q2_1 AS [HFC Avail], a.q2_2 AS [HFC Advice], a.q2_3 AS [HFC Easy Process], a.q2_4 AS [HFC Overall],
                 a.q3_1 AS [LSII Avail], a.q3_2 AS [LSII Inform], a.q3_3 AS [LSII Easy Close], a.q3_4 AS [LSII Overall], a.q4 AS [Close Date],
                 a.q5 AS [Close Costs], a.q6 AS Recommend, a.q7 AS Comments, a.LoanNum, a.ATS_Source AS [Bus Par], a.Fulfillment_Source,
                 a.Relo_source, a.third_party_ref, a.AE_Name AS HFC, a.aenum AS [HFC Num], a.LSII, a.RAM, a.TM, a.date_received AS [Dt Received],
                 a.AppDt, a.PhaseDt, a.PurposeCd, a.MailStandardAdd, a.MailCity, a.MailState, a.MailZip, a.AreaCode, a.PhoneNum, a.BEMail AS Email
     FROM tmpFundEMAIL a WHERE a.LoanNum NOT IN (SELECT DISTINCT [LoanNum] FROM tblMasterFundEMAIL)

2) Run the query and verify you are receiving the data you expect to see.
3) INSERT INTO tblMasterFundEMAIL (SELECT * FROM Query1)
0
 
chrisamuelAuthor Commented:
I'm afraid that's not quite what I'm looking for. The thing is I receive 4 different types of excel files on a biweekly basis. 2 of the excel files append to the same table but one is received via mail and the other, email. So I first have two macros that do a transfer spreadsheet into temp tables. Then I have two appends that gets only the data I need and changes to data types I want to two tables (Mail and Email). I append these two tables to a main table with a field of "Yes" or "No" to know if it's mail or email. And then I have two more sets of excel files (same thing but different category). Anyways, I think this problem is caused by the data type changes that I'm making when I append the temp files with type text [LoanNum] (from excel) into a table that has a number. I was wondering if you had any experience having to move data from excel to access.
0
 
Steve BinkCommented:
>>> I'm afraid that's not quite what I'm looking for

How is it not what you're looking for?  This takes the query you wanted to run and inserts its results into your tblMasterFundEMAIL.  Sure, it's a two-step process, but are you saying it does not work at all?  Your original question states:

>>> My append query won't work once I add this where clause

Moving data from Excel to Access is pretty simple, and you have multiple methods to use.  Overall, it is easier to manipulate the data once you have it in Access, and it sounds like you're already doing that.  What else do you need help with?
0

Featured Post

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.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now