query with column with duplicate data

[inkman]

I am so stumped I am not sure if this is the correct forum for the question. apologies in advance. I am trying to produce a query that creates an out put the same way a report would.I need to do this in order to export the data to an emailing application. I chave generated a query to filter by newsletterID, in the newsletter form I can select products that i wish to include in the newsletter, and I can select which group I wish to send this newsletter to. that is al great. Now if for example I was to create a report it would show the in the 'header' section the address details of 'one' addressee and in the 'detail' section it would list the items and their respective details. however, if i look at the query it shows all that information but for every product record I also have the name of the addressee. If five products I have the name five times (which the email software would send five times). I am trying to work out how I can get the resulting query to show one addressee and all the products (like a report).a bit more detail:
Newsletter form provides:
NewsletterID and Buying GroupID
Newslettersubform provides:
ProductiD and details (calculations)
My email query
takes this info and link to the buying group and marketing table (ie users and email addresses)linked to the Newletter form query via Buying groupID)

I could create a report and automatically attach it to each email, but people will not bother opening the report so i want to include the data in the body of the email.
any help gratefully received.

Ian

 

[lespaul]

It sounds like you have a query that returns the results like this:
[tt]
ProductName emailaddress
Product1 someemail@nowhere.com
Product2 someemail@nowhere.com
Product3 someemail@nowhere.com
Product5 anotheremail@somewhere.com
[/tt]

and you want to get them like this instead:
[tt]
ProductName emailaddress
Product1 someemail@nowhere.com
Product2
Product3
Product5 anotheremail@somewhere.com[/tt]

is that what you're trying to do?



Leslie

Come join me at New Mexico Linux Fest!

 

[inkman]

Hi Leslie, thanks for the reply. that is pretty much it.
It is actually :
email address: abc@1234.com ; Product: Product1
email address: abc@1234.com ; Product: Product2
email address: abc@1234.com ; Product: Product3

I wondered if I could somehow concatenate so that the record looked like this:

email address: abc@1234.com ; Product: Product1 ; Product2 ; Product3

Any ideas much appreciated. I may be approaching it totally incorrectly.

Ian

 

[lameid]

faq701-3499

Depending on how you want to use the resulting data, you may want to modify the code to do something else like do something with the value instead of putting it in a temporary table.

 

[inkman]

Thanks for the link lameid, it looks pretty complicated (for my skill level), but also appears to do what I want to do. I will study this and see if I can work it out. Literally I want to send out a newsletter (hence email addresses) with each of the products details (ie part no and price) in the body of the email.

 

[lespaul]

it's really not that complicated....just follow the instructions and if you have questions just come ask...most of us are familiar with the function and can help you use it!

Leslie