Mail Merge with Access

[missesclick]

I need to do an email mail merge with data from Access. I have an Access table or query with data in multiple rows that needs to be merged into a single email. The data in Access looks something like this:



student school school address email

 
Lisa Singer Andover 32 Andover Street lsinger@e.com
Lisa Singer Kew-Forest 6 90th St. lsinger@e.com
Lisa Singer Clinton School 80 Tremont St. lsinger@e.com
Dan Jones Clinton School 80 Tremont St. danj@l.com
Dan Jones Andover 32 Andover Street danj@l.com
Jim Smith Kew-Forest 6 90th St. smith@gm.com

I want to send an email to each student that would look something like this:

Dear Lisa,

We have sent your application to the following schools:

 
Andover 32 Andover Street
Kew-Forest 6 90th St.
Clinton School 80 Tremont St

.



When I put it into Word, I get 3 messages for Lisa, each with a separate school listed. Last year I tried 4Tops Mail Merge for Access software, but somehow it never worked right. I work for a non-profit so we don't have money to spend for consultants. But we have a high volume of work, and need this to go out by email if at all possible.

Please help!!

[ajetrumpet]

When I put it into Word, I get 3 messages for Lisa, each with a separate school listed.

that's what is supposed to happen with data like what you posted. is that now what you want?

If not, I would suggest querying out what you want from the table before doing the merge.

[missesclick]

No, I want one email for Lisa with all 3 of her schools listed on it. The problem is that in the Access tables and queries each record for the schools associated with Lisa is a separate row. How can I get one row for Lisa with the schools listed in columns? Then I should be able to do a mail merge with Word.

[ajetrumpet]

you'll have to transform the data using a recordset, I'm sure. Just looking at it, a crosstab might not do it, but I can't say 100%. not experienced enough to say just be guessing.

[missesclick]

Thanks. I looked at crosstab queries and PivotTables, but neither one was what I wanted.

I don't know of any way to do it using recordsets.

Thanks for your help, though!

[ajetrumpet]

I'm sorry misses. I don't have time to write a sample function for you right now. hopefully someone else can.

good luck!

[missesclick]

Thanks, anyhow!