I'm building a database with SQL Express 2005 for the call log for our fire department. I've simplified my database here to show the problem I'm having.

I have a table "CallLog" with info such as call number, address, date, time of call, etc. I also need to keep track of the names of the people who went on the call which could be anywhere from 1 to 80 people, but normally 3 to 5. So I have a second table "Personnel" which has just two fields: the primary key associated with the call (from the CallLog table) and the name of the person.

My problem is when I display all of the data in a grid, how should I display the names of the people? I was first going to create a column and do a subquery to list the names separated by commas:

342 12:45 123 Main Street John Doe Bill, Mike, Fred
343 9:32 567 Oak Street Jane Smith Mike, Dan, Bob, Gary

1. If I choose to do this, will I need to loop through every record in the CallLog table and query the Personnel table to build a string for this last column or is there an SQL statement that can accomplish this?

2. Is there a better solution to what I'm trying to accomplish?

Greg