Solved

How do I add criteria to an Append Query?

Posted on 2014-01-31
16
313 Views
Last Modified: 2016-03-24
I am getting ready to import our 2013 PayPal data into our MS Access donor tracking database so I can send out annual receipts.  About two dozen of the 2,100+ records have already been entered manually by me because the donation was for $250 or more, it was a first-time donation, the donor requested a receipt right away, etc.  The query works, but now I want it to NOT append the data record if the PayPal Transaction ID (a unique alphanumeric string) is already present in the donations table.  Here is what the query looks like:Donnation-Append-Query.jpg.  So what do I put in the criteria box in the Transaction ID column so that the record is ONLY appended if [1PayPalDataToImport].[TransactionID]<>[tblDonations].[strDocumentID]?  The text of the query is attached.  Or am I going about this all wrong?

Thanks.
DonationQueryText.txt
0
Comment
Question by:bobbat
  • 8
  • 4
  • 2
  • +2
16 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
use this..


 Field      [TransactionID]
Table      [1PayPalDataToImport]



Criteria   Not In(Select strDocumentID from tblDonations)
0
 

Author Comment

by:bobbat
Comment Utility
After about one minute (there are about 10,000 records in tblDonations), I get the following message:Query Dialog Box
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?
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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
0
 

Author Comment

by:bobbat
Comment Utility
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.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
Comment Utility
try first this query

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]
0
 

Author Comment

by:bobbat
Comment Utility
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. . . .
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
<<<<<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
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
<<<<<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
0
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

 

Author Comment

by:bobbat
Comment Utility
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
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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.
0
 

Author Comment

by:bobbat
Comment Utility
I may try that,  thanks.
0
 

Author Comment

by:bobbat
Comment Utility
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:

Proposed Append Query"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));"
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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
0
 

Author Comment

by:bobbat
Comment Utility
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:PayPal Transaction ID Conversion by Excel to Scientific Notation
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:Today's Donkey Rescue in Honda Minivan
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
0
 

Author Closing Comment

by:bobbat
Comment Utility
Many thanks to all that commented on this question including the PawTrax lead.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

13 Experts available now in Live!

Get 1:1 Help Now