As a secondary thought would it be easier:
I have 20 perishable items.
If the part number stayed constant (ie 1001-Milk), but there was an attached list view that showed the all the batches.
When the part was entered on the invoice, it would also reference the 'oldest' batch that still had inventory available, possibly in the description or as a separate column on the invoice?
I'd appreciate any thoughts on this matter!
You should use two separate tables for this: a table of Products, with only general information about each product (e.g. ProductID = 254; ProductName = "Milk" ), and another table for the actual inventory (e.g. InvenoryID = 4567; ProductID = 254; Quantity = 20; ExpiryDate = 3/1/2009), with each batch being a separate record.