>>I have a table that has six fields which hold IDs from the same table. Each field can have different IDs. Example:
>>
>>Contract Table:
>>
>>ContractID
>>CustID1
>>CustID2
>>CustID3
>>CustID4
>>CustID5
>>CustID6
>>
>>
>>Customer Table:
>>
>>CustID
>>First Name
>>Last Name
>>
>>In the Contract table the CustIDx fields are all referencing the same Customer table. However, CustID1 will be different than CustID2, etc.
>
>I was wondering why you have those six CustIDx fields. It suggests that your tables aren't normalized. Are there really exactly six customers associated with each contract?
The contract is for insurance and the six customer fields are for the different types of owners that can be associated. There are exactly six different types of owners (Primary,Joint,etc.). This table is normalized but just unique in what it stores.
The other UT entries helped me figure out how to handle this. I needed to use an alias for extract each customer.
-----------------------------------------
Cathi Gero, CPA
Prenia Software & Consulting Services
Microsoft C# / .NET MVP
Mere Mortals for .NET MVP
cgero@prenia.comwww.prenia.comWeblog: blogs.prenia.com/cathi