Microsoft Access - Access 2007 Append Query - Asked By D on 05-Dec-11 07:37 PM

microsoft access 2007 why is my append query appending a single record multiple times.  Here is the SQL

INSERT INTO Donation ( ContactID, Donation, Comments, Donationdate, PaymentType )
SELECT [2011 Walk Payment Import].ContactID, [2011 Walk Payment Import].AdultRegistrationAmt, [2011 Walk Payment Import].Comments, #11/1/2011# AS Expr1, "Walk Registration Fees 4171.20" AS Expr2
FROM [2011 Walk Payment Import] INNER JOIN Donation ON [2011 Walk Payment Import].ContactID = Donation.ContactID
WHERE ((([2011 Walk Payment Import].AdultRegistrationAmt)>0))
ORDER BY [2011 Walk Payment Import].ContactID;

There is a DonationID autonumber field in the destination Donation table - which seems to be the cause.  For every record an individual already has in the Donation table, the append query is replicating the record to be appended by that amount.

How do I fix this?
Robbe Morris replied to D on 05-Dec-11 07:56 PM
It found more than one record in the FROM/WHERE clause that met your conditions.  What were you expecting to happen?

You may want to try an IN clause

from [2011 Walk Payment Import]
where ContactID in (select ContactID from Donation)
and some other conditions
D replied to Robbe Morris on 05-Dec-11 08:07 PM
Thanks for your quick response.  I'm not very experienced with SQL, would what you suggested replace part of the SQL I previosly noted?

I was expecting the record(s) from the append query be appended to the Donation table and a new DonationID autonumber be generated.  These new records should not affect or be affected by the records currently in the Donation table.

Thank you again.
Robbe Morris replied to D on 05-Dec-11 08:31 PM
Well, your code will append the same records over and over again each time this statement is run.  Is this really what you are after?

No, everything before the FROM is fine.
D replied to Robbe Morris on 05-Dec-11 08:35 PM
This is a one time only append.   Moving records from another database.

What should go after the FROM?

Thank you.
wally eye replied to D on 05-Dec-11 08:39 PM
I don't understand why you would join it back to the Donation table:

INSERT INTO Donation ( ContactID, Donation, Comments, Donationdate, PaymentType )
SELECT [2011 Walk Payment Import].ContactID, [2011 Walk Payment Import].AdultRegistrationAmt, [2011 Walk Payment Import].Comments, #11/1/2011# AS Expr1, "Walk Registration Fees 4171.20" AS Expr2
FROM [2011 Walk Payment Import]
WHERE ((([2011 Walk Payment Import].AdultRegistrationAmt)>0))
ORDER BY [2011 Walk Payment Import].ContactID

will append all records from [2011 Walk Payment Import] to Donation, where the AdultRegistrationAmt > 0.  Are you trying to restrict or qualify further the records you are appending?
D replied to wally eye on 05-Dec-11 08:43 PM
AWESOME - you are brilliant - thank you!!!!
wally eye replied to D on 05-Dec-11 08:56 PM
You're most welcome, thank you for the enthusiastic feedback!