Multiple lookups in one record
We are a printing company and I have two related tables, Orders and Prices.
In the Orders table, I have 4 fields for 4 items (Item 1, Item 2, Item 3, Item 4) and each one has a paper field (Item 1 paper, Item 2 paper...) because each item uses a different paper.
In the Prices table, there are fields Paper name, Paper size, and Paper price.
For each item in the Orders table, I want related fields Paper size and paper price to come over. I set Item 1 paper and Paper name as match fields and size and price come over like a charm. Then setting Item 2 paper and Paper name as match fields didn't work; the size and price were still related to the item 1 paper.
How do I designate each item as a separate relationship?