How do I add criteria to an Append Query?

After about one minute (there are about 10,000 records in tblDonations), I get the following message:
I got one of the Transaction IDs that I know I entered in the donations table (via a form) and searched the donations table, and it was present.  I got a Transaction ID that I have not entered manually and it was not present.  Any ideas?

The Sub-Query method should have nailed it ... That's what I would have recommended as well.  

Another option is to import the PayPal transactions into a separate temp table.  Then create an Append query  left joining the temp table with the tblDonations while setting the Criteria of the strDocumentID field in tblDonations to Is Null which is essentially all the UnMatched transactions.  

ET

ET, can you clarify your suggestion?  

I'm confused because people donate and the records in the PayPal table have to be assigned to a donor--hence the People and Donor tables in my original query.  In my Append Query, I don't even "see" the Donations table (in the Query By Design view)--that is just where the PayPal records go.

That worked--it showed me that my Excel data that I imported into Access is scrambled compared to the Excel data I downloaded from PayPal.  Probably due to the way Excel sorts data, even though I select the "Expand the Selection" button.  It may take a few days to recreate the data table correctly. . . .

<<<<<ET, can you clarify your suggestion?  
I'm confused because people donate and the records in the PayPal table have to be assigned to a donor--hence the People and Donor tables in my original query.  In my Append Query, I don't even "see" the Donations table (in the Query By Design view)--that is just where the PayPal records go.>>>>>

Rey Obrero answered this part of your question with his comments below, that's what I was referring to.

<<<<Select *
From [1PayPalDataToImport]
Left join tblDonations On [1PayPalDataToImport].[TransactionID]=[tblDonations].[strDocumentID]
where [tblDonations].[strDocumentID] is null

this will give you all records in [1PayPalDataToImport] that are not in tblDonations based on the
fields [1PayPalDataToImport].[TransactionID] and [tblDonations].[strDocumentID]>>>>>


ET

<<<<<That worked--it showed me that my Excel data that I imported into Access is scrambled compared to the Excel data I downloaded from PayPal.  Probably due to the way Excel sorts data, even though I select the "Expand the Selection" button.  It may take a few days to recreate the data table correctly. . . .>>>>>

I'm not sure I'm following this comment.  If your PayPal transactions are in Excel, when you import them into an Access table you should see the same data.  Please explain further.

Also, I would first create the Temp Table in Access with the exact field names that you are using.  Then establish those same field names at the top of your Excel data.  Last, make sure the Excel worksheet contains only the "Valid" Rows and Columns of data to be imported.  Put your cursor in Cell A1 then press End+Home Keys to verify which rows are included in the current worksheet.  Another option is to Range Name the data (Headers, Rows and Columns) and use the Range Name when importing into Access.  Either way will work.

ET

ET: Not sure if you have ever run an animal charity before but it's a lot harder than in looks (see: https://www.facebook.com/bhfer.tb), especially with horses.  There are two main reasons why PayPal data is not the same as Access data: 1.  Access data is meant to be reported to the IRS since theses are tax deductible donations and 2.  I get asked by donors questions like "Which horses did I donated to last month?"  This rescue was built by little old ladies and small donations in the early years and these people are still with us.  But some have failing memories and need to see it in writing--sometimes several times--about what they gave, to who, and when.  Oh boy . . .

As an example of #1:  A PayPal donor name is "BigManOnCampus".  Huh?  Who the hell is that?  Certainly not someone the IRS knows.  So I use Spokeo and/or internet searches and reverse search their address and/or email to get a real person.  Sometimes I have to email the donor and ask.  Women are notorious for changing their PayPal name subtly:  They will spell out their middle name, they will add/remove a married name, they will use a nick name, they will remove a period after an initial, they will use an initial instead of a first name, etc.  When it comes to doing an annual receipt, I get emails stating "What about . . ."  The bottom line is it takes hours to figure out who is who.  I'll rather take a check but people want to use PayPal.  What can I say--we have 40 horses and own about 200 more that we have adopted out so we need every dollar.

As an example of #2:  The Donations table is in a one to many relationship with the Donations Details table.  We have a donor that sometimes gives different things to 19 different horses and each horse is a separate record in the Donation Details table.  I do these manually if the money if for more than one horse, cause, event, fundraiser, etc.  Some donations are not tax deductible, i.e., the person purchased something as a fundraiser.  Thus a lot of fields are added to the PayPal data based on what a donor puts in the PayPal Notes field.

So a lot of work is done in Excel but Excel does not sort like Access and I made some mistakes and scrambled the data which I am still trying to fix now. . .
Donation-Database-Screen-Shot.jpg

If the PayPal ID is unique, then add a unique index to your Access table.  This does not need to be and probably shouldn't be the primary key since I'm sure you get data from other sources.  When you run the append query, you'll get a warning message that tells you that some records could not be appended because they would create duplicates.  You can ignore the message because that is what you want.

Just make sure the default for the PayPalID field is null.  Some versions of Access will set the default to 0 which won't work for you.

I may try that,  thanks.

Rey, I checked the data and your Select query,

"Select * From [1PayPalDataToImport] Left join tblDonations On [1PayPalDataToImport].[TransactionID]=[tblDonations].[strDocumentID] where [tblDonations].[strDocumentID] is null"

worked and showed me the 2010 queries I need to import.  I think this will work:

"INSERT INTO tblDonations ( lngDonorsID, strSourceWebsite, strDocumentID, datDonationDate, strDedication, lngDocumentID_Type, lngDonationTypeID, lngDonationUseID, curTotalValue, curNetTotal, curTransactionFee_Donor, strDonationDescription, curShippingAndOrHandling, curInsuranceAmount, curTaxPaid, [bol Recurring], strGiftMessage )
SELECT DISTINCT tblDonors.lngDonorsID, "PayPal.com" AS Expr1, [1PayPalDataToImport].TransactionID, [1PayPalDataToImport].Date, [1PayPalDataToImport].strMemorialDedication, [1PayPalDataToImport].lngDocumentID_Type, [1PayPalDataToImport].lngDonationTypeID, [1PayPalDataToImport].lngDonationUseID, [1PayPalDataToImport].Gross, [1PayPalDataToImport].Net, [1PayPalDataToImport].Fee, [1PayPalDataToImport].strItemTitle, [1PayPalDataToImport].ShippingandHandlingAmount, [1PayPalDataToImport].InsuranceAmount, [1PayPalDataToImport].SalesTax, [1PayPalDataToImport].bolRecurring, [1PayPalDataToImport].strGiftMessage
FROM ((1PayPalDataToImport LEFT JOIN tblDonations ON [1PayPalDataToImport].TransactionID = tblDonations.strDocumentID) LEFT JOIN tblPeople ON [1PayPalDataToImport].strPayPalDonorName = tblPeople.strPayPalDonorName) LEFT JOIN tblDonors ON tblPeople.lngPersonsID = tblDonors.lngPersonsID
WHERE (((tblDonations.strDocumentID) Is Null) AND (([1PayPalDataToImport].TransactionID) Is Not Null));"

Hi bobbat ...

If the solutions are not producing the results you are expecting, please post a sample of your two tables so we can see what types of data you are working with.

Thanks,

ET

I created a table with the query from Rey, exported it to Excel, imported it back into Access and processed it with the set of Append Queries I usually use.  It was painful but I got the data in.  I just wish there was an easier way.  One issue that I have with this method is when PayPal assigns an all numeric Transaction ID, Excel converts it to scientific notation.  Then it's problematic to import that as a 17 character PayPal ID.  So I have to create another column as save the value as text.  I wonder how other charities do it. . .

For example:
Will assign point tomorrow--have to go check on a new addition to the rescue.

PS: We brought a dying (starving) donkey back to the rescue in the back of our minivan today.  He was too weak to stand:

Very sad :(

On a side note, not for points, we have another regular 'asker'  in the Access Topic area who is involved with Animal Rescue groups.  They have a Access database that handles many aspects of animal rescue (impounds, adoptions, medical, donations, you name it), which they make available to other like-minded organizations (I don't know the price).  More info here, if you are interested:

http://spcala.com/pawtrax/pawtrax.php

Many thanks to all that commented on this question including the PawTrax lead.