appending query trouble (1 Viewer)

[Chrisopia]

I use an appending query which sorts out ordertype= 3 and adds them to tblInvoice,

trouble is, it worked for 1 record, but when i added a second record it added the first one as well, giving me three records when there should be 2... and the third one added the previouse 2, giving me 6 records in tblInvoice instead of 3.

The only way I could think of filtering it further would be to not add the OrderID if it has a Description ID attached that is already there. Does this make sense? (hope the diagram below helps)


Relationship:
tblOrder (OrderID) --- < tblDescription (OrderID & DescriptionID*)
--- < tblInvoice (OrderID & Invoice Number*)

* = primary key
(tblOrder has both tblDescription and tblInvoice attached by OrderID)


Basically an order can have many descriptions, but the append query should only sort out individual descriptions where ordertype = 3. Ordertype is a field in tblOrder...

can anyone help?

 

[jardiamj]

I understand what you want to do. And yes, it makes a lot of sense. You could create an unmatched query of the Orders where ordertype = 3 and the OrderID is not in tblInvoice. And use it to run your append query.

Cheers!