Create delimited file

[mshmyob]

I have two tables that are related by a ContactID field that allows each contact to have multiple email addresses (ie: 1:M).

tblContact-> ContactID, ContactFName, ContactLName, ContactCompany, CategoryID
tblContactEmail-> EmailID, ContactID, EmailDesc

I have created a simple Select query like:

SELECT tblContact.ContactFName, tblContact.ContactLName, tblContact.ContactCompany, tblContactEmail.EmailDesc
FROM tblContact INNER JOIN tblContactEmail ON tblContact.ContactID = tblContactEmail.ContactID
GROUP BY tblContact.ContactFName, tblContact.ContactLName, tblContact.ContactCompany, tblContactEmail.EmailDesc, tblContact.CategoryID
HAVING (((tblContact.CategoryID)=7));
 

The result from this query simply displays all the contact names, company names, and email addresses of all contacts classified as Clients.

Each contact's email is on a seperate line. I however need to combine the email addresses of any contact who has the same ContactID on a single line for export to Outlook address book.

Is there a way to do this in SQL and if so how?

P.S. - I am trying to export the contacts to an Outlook address book but this way creates multple entries for anybody who has more than one email address since I cannot map the field to email address 2 and email address 3 in Outlook but I can if I can get them on one line. Unless someone with better knowledge of Outlook than me has a better way of doing it.

cheers,

[Lysander]

You could write a function, say MakeMail() that accepts the contactId. This function could then loop through tblContactEmail and build up a string of all the email addresses. MakeMail would the return the string of 0, 1 or more email addresses.

Then use MakeMail(contactId) in your query

[mshmyob]

Thanks Lysander, I thought there might not be a "pure" SQL approach but I was just checking.

cheers,