first off, I've never posted on a forum in regards to access so forgive me if I left off vital information.
I am designing a database for the packaging of produce. it includes an incoming PO entry table, order recieving table, packaging table, yada yada. the primary key for all the tables is the lot number of the product. The issue arises when we need to mix the lots. I cant even fathom a format that would allow me to easily and 'normally allow the system to show that product is comprised of multiple lot numbers. do I add a field to the packaging table for each lot that is mixed in? should I have separate tables for each lot? create a different table for only the lot numbers with a relationship to the packaging table?
does anybody have any ideas? I'm at a loss.