Append Query appending twice. Why?

[crispywafers]

Hi all,

I'm stuck. Maybe someone can tell me why.

I have an append query that takes certain information from the orders
table (OrderID, Trimester Amount Due) and appends the information to
the July invoices table where an InvoiceID is created. It is supposed
to append the order ID, and the trimester amount due from the order
table. A new order is created when there is a new School year...
therefore a new invoice for that July school year needs to be created
(hence the append query).

My problem is ... (say I already have July invoices appended for
9/1/2004) I'll run it for 9/1/2005 and 9/1/2005 invoices will come out
correctly with the correct order ID, etc (and only 1 invoice). BUT last
year's orders, 9/1/2004, which were correct before (with only 1
invoice), will now have ANOTHER invoice. Basically it's not just
appending for the year I'm asking it to append for. It's appending for
2005 once, and then appending 2004 invoices again.

Here is the code:

PARAMETERS [Specify School Year] DateTime;
INSERT INTO [July Invoices] ( OrderID, TriAmountDue )
SELECT Orders.OrderID, Orders.JulyTuition
FROM Student_Records INNER JOIN (Orders LEFT JOIN [July Invoices] ON
Orders.OrderID = [July Invoices].OrderID) ON Student_Records.StudentID
= Orders.StudentID
WHERE (((Exists (SELECT * FROM Orders WHERE OrderID = [July
Invoices].OrderID AND SchoolYear = [Specify School Year]))=False)
AND ((Student_Records.Active)=True));


The code is supposed to append information only if the information with
that school year isn't already in the table.

Help? Please?

 

[david epsom dot com dot au]

Your test only tests if invoices exist in the current school
year. Invoices that already exist in the current school year
are not created. Your test explicitly does NOT exclude
Invoices that exist from a different year.

perhaps you meant something like this (exclude where exists):

WHERE (((Exists (SELECT * FROM Orders WHERE OrderID = [July
Invoices].OrderID ))=False)

or this (include ONLY current school year)

WHERE (((Exists (SELECT * FROM Orders WHERE OrderID = [July
Invoices].OrderID ))=False)
AND (SchoolYear = [Specify School Year])

(david)