Hi All,

I have a problem with a formula.

Table1 - lots
lotID
warehouseID
QTYonHAND
QTYReservered

Table3 - Invoice
invoiceID

Table2 - invoicelines
invoicelinesID
invoiceID
lotID
quantity

Table 1 has all of the information i need except the quantity on invoicelines. This is only used if that line is associated with the LotID and may or may not be linked.

therefore, I can get Qty On Hand, and Qty Reserved, but errors when I include Quantity on Invoice.

Error makes QTYonHAND to appear as many times an Invoice with that lot ID is found.

I realise this is not an easy error to explain but would appreciate any help. I have included a isnull statement, which makes the Quantity field correct, but multiples QTYonHAND for each Quantity found.