I have a standard Orders table with Order Details for each line item. My issue is that the Order Details line can come from one of several tables.
For example the Order Details line which stores the primary key of the item being sold could be from the Inventory table, the Custom table, the Rental table, etc.
Seeing as each of these item tables have the same autonumber primary keys, I need to figure out a way to mach the items being sold to the Order.
My current train of thought is to create a different Orders Details table for each table that includes items that can be sold. Would this design be sufficient? Or is there a better way to go about it?
Unfortunately it is too late to assign unique primary keys to each tables (i.e. appending a special character to the primary key to indicate which table it comes from).
I am also having trouble trying to figure out how query the Order Details tables so that they can be viewed on the Order form.