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

x
?
Solved

How do I add criteria to an Append Query?

Posted on 2014-01-31
16
Medium Priority
?
344 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
[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
  • 8
  • 4
  • 2
  • +2
16 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39825305
use this..


 Field      [TransactionID]
Table      [1PayPalDataToImport]



Criteria   Not In(Select strDocumentID from tblDonations)
0
 

Author Comment

by:bobbat
ID: 39825373
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
ID: 39825393
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
Industry Leaders: 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!

 

Author Comment

by:bobbat
ID: 39825524
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39825539
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
ID: 39825660
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
ID: 39825760
<<<<<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
ID: 39825762
<<<<<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
 

Author Comment

by:bobbat
ID: 39827166
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 39

Expert Comment

by:PatHartman
ID: 39827809
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
ID: 39827858
I may try that,  thanks.
0
 

Author Comment

by:bobbat
ID: 39828112
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
ID: 39828348
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
ID: 39831422
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
ID: 39832050
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
ID: 39842956
Many thanks to all that commented on this question including the PawTrax lead.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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 …

604 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