Database for Fire Department query help

[ghall426]

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

[si_the_geek]

As far as I know, there is no built-in method for doing what you want.

Depending on your experience and requirements, a Stored Procedure may be the best option, or it may be better in code. To help us determine, we need more details:

Will this style of display only be in the one program?

Will it only be for this one set of data? (Personnel & CallLog)

What language/connection method/etc are you using for this? (eg: VB6 with ADO, VB.Net 2003, ...)