Concatenate fields as a result of duplication 1

[bikerted]

I have a query pulling contact details together for a mail merge letter together with date(s) and cost of a service. The clients/letter recipients have a unique client ID and the service provided for them is identified by a unique service ID. Everything runs smoothly when I run the query and each client has one service event,one price and one date. However, if they have say three service events for say £10.00 each, three letters are generated all to the same address. I'd rather one letter with the dates brought together - perhaps punctuated by commas and the amount totalled. So far I've envisaged a conditional expression in the cost field that says "if Client ID is duplicated add together each separate cost" and in the date field "if Client ID is duplicated concatenate the dates". I also need to stop the letter triplicating!

Has anyone any ideas and perhaps the SQL/expressions to match?

I'd be more than grateful.

Ted.

 

[PHV]

Take a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886