Unable to append due to key violations

[Abigael]

I'm stuck.

I have two tables - an orders table (Orders) and a invoices table
(InvoicesFirst). They are setup as a 1 to many relationship...sharing
OrderID. They each have their own primary key - OrderID and InvoiceID
respectively...both assigned to autonumber.

I'm trying to append data from tables (Student Records) and (Orders)
as well as data returned by a module into the Invoices table. When I
run the query in datasheet mode to test - the query returns the
information correctly. But when I actually try to run the append query
- I'm getting "unable to append due to key violations" for all the
records.

I can't figure it out - as I'm not seeing any duplication, etc. with
the keys. I think the problem is coming from trying to append the
OrderID from the Orders table to the InvoicesFirst table - as when I
removed that - it was able to append. But I obviously need that
information to append - as that is what will tie the two tables
(Orders and Invoices) together.

Here is the Query:

INSERT INTO InvoicesFirst ( OrderID, StudentID, StudentFirst,
StudentLast, Grade, Dismissal, EarlyBird, Discount, ProgramFullAmount,
EarlyBirdTriDue, ExtendedDayTriDue, FirstDiscountTri,
FirstTriAmountDue )
SELECT Orders.OrderID, Orders.StudentID, Student_Records.SFirstName,
Student_Records.SLastName, Student_Records.Grade,
Student_Records.Dismissal, Student_Records.EarlyBird,
Student_Records.Discount, FormatCurrency(PrintFirstTri([Dismissal],
[Grade],[EarlyBird],[Discount])) AS ProgramFullAmount,
FormatCurrency(PrintEarlyBirdTri([Dismissal],[Grade],[EarlyBird],
[Discount])) AS EarlyBirdTriDue,
FormatCurrency(PrintExtendedDayTri([Dismissal],[Grade],[EarlyBird],
[Discount])) AS ExtendedDayTriDue,
FormatCurrency(PrintFirstDiscountQuery([Dismissal],[Grade],[EarlyBird],
[Discount])) AS FirstDiscountTri,
FormatCurrency(PrintFirstTriQuery([Dismissal],[Grade],[EarlyBird],
[Discount])) AS FirstTriAmountDue
FROM Student_Records INNER JOIN Orders ON
Student_Records.StudentID=Orders.StudentID
WHERE (((Orders.SchoolYear)=[Forms]![frmNewYear]![txtSchoolYear]));

Any help would be appreciated..I've been stuck on this for almost 2
days.

 

[Jeff Boyce]

Have you run a "duplicates" query to look for duplicate primary key values?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.