I have a database that is for furniture warranty claims. When someone calls in to create a claim, sometimes they have more than one piece they want to have repaired. So in the cases table, I have the furniture item code fields 1-4 so that a customer can make a claim for up to 4 pieces at a time. So for each item code there is a Problem Type field 1-4 (explaining the nature of the problem for the corresponding item code) and a Problem Detail Field 1-4 (giving details of the problem for the corresponding item code).
My problem is that I would like to be able to make reports and do cross-reference searches for certain item codes with certain problem types/details but I don't want them to mix up item code 1 with problem type 3 for instance. Is there a way to tie the Problem Type 1 and Problem Detail 1 fields to the Item Code 1 field and so on? Is there some way I should be structuring it differently instead?
Any help is greatly appreciated.