Okay, here goes, I'll give it my best shot to describe this. There are four tables involved. (Reminder: This is a third party application. I can add tables and columns...

Thanks Grant! I may go that route. But, just to provide a comprehensive problem statement, please see my next post.

Disclaimer: I'm new to SQL Server this year and I have not tried this solution. However, it may provide you with some ideas or prompt a more experience SQL Server...