Multiple Donations - Mail Merge

[Sarah]

I have been trying to find the answer to this question in so many different
ways. I am at a loss now. I hope someone can help.

I have an access db with two tables. Donor Info and Donations. I have a
mail merge that prints thank you letters to those folks who donate during
that time. However, sometimes people donate twice or more during a time
period. Instead of writing two or more letters, I would like to combine the
donations to one letter. So it would say thanks for donating 1/10/09 $50 and
1/15/09 $60. I found a mail merge way to do it except it won't work with my
layout that I need in word. So I was hoping I could find an easy way to have
multiple columns in a table or query. I don't care if there are multiple
steps as long as I can automate them. I was thinking I could do a make a
table and update combination.

Any thoughts would be super helpful and appreciated!
Thanks.

 

[Sarah]

I have used the concatenate function before. I think it would work here
again. But how do I tell it that if the count of the donor is 1 (ie they
only made one donation during time period) to still concat?

 

[John W. Vinson]

I have been trying to find the answer to this question in so many different
ways. I am at a loss now. I hope someone can help.

I have an access db with two tables. Donor Info and Donations. I have a
mail merge that prints thank you letters to those folks who donate during
that time. However, sometimes people donate twice or more during a time
period. Instead of writing two or more letters, I would like to combine the
donations to one letter. So it would say thanks for donating 1/10/09 $50 and
1/15/09 $60. I found a mail merge way to do it except it won't work with my
layout that I need in word. So I was hoping I could find an easy way to have
multiple columns in a table or query. I don't care if there are multiple
steps as long as I can automate them. I was thinking I could do a make a
table and update combination.

Any thoughts would be super helpful and appreciated!
Thanks.

See http://www.mvps.org/access/modules/mdl0004.htm

This will let you concatenate a field - or fields - from zero, one, two or
more Donations records into a string for your mailmerge.

 

[Sarah]

Thank you but this doesn't quite work... Maybe I am missing something. But
I also need it to have a where clause (from date to date)..

 

[Sarah]

Is there some way to modify this to indicate that it is okay to have just one
field?

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ")
As String



Dim rs As New ADODB.Recordset

rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim strConcat As String 'build return string

With rs

If Not .EOF Then

.MoveFirst

Do While Not .EOF

strConcat = strConcat & .Fields(0) & pstrDelim

.MoveNext

Loop

End If

.Close

End With

Set rs = Nothing

If Len(strConcat) > 0 Then

strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))

End If

Concatenate = strConcat

End Function

 

[John W. Vinson]

Is there some way to modify this to indicate that it is okay to have just one
field?

What are you using for pstrSQL? You can put whatever you want in it - the
Concatenate function will concatenate whatever is in the first field of the
recordset. That can be one field or a composite of several fields, as you see
fit!

There is NOTHING in the code that prevents it from having just one field. What
symptom are you seeing?

 

[Sarah]

I am getting no values in my concat fields...

What are you using for pstrSQL? You can put whatever you want in it - the
Concatenate function will concatenate whatever is in the first field of the
recordset. That can be one field or a composite of several fields, as you see
fit!

There is NOTHING in the code that prevents it from having just one field. What
symptom are you seeing?

 

[John W. Vinson]

I am getting no values in my concat fields...

Please post your query in SQL view.

 

[Sarah]

SELECT DISTINCT Concatenate("SELECT [date posted] FROM concatbatchtest WHERE
[id] =" & [id],",") AS [Date], Concatenate("SELECT [amount] FROM
concatbatchtest WHERE [id]=" & [id],",") AS Amount, concatbatchtest.ID
FROM concatbatchtest;

 

[John W. Vinson]

SELECT DISTINCT Concatenate("SELECT [date posted] FROM concatbatchtest WHERE
[id] =" & [id],",") AS [Date], Concatenate("SELECT [amount] FROM
concatbatchtest WHERE [id]=" & [id],",") AS Amount, concatbatchtest.ID
FROM concatbatchtest;

Normally you'ld use a query on a "one" side table, and concatenate records
from a "many". What's actually in the table concatbatchtest? Are you intending
to concatenate just one date and just one amount for each record? If so, why
concatenate at all?

What's the context? What other tables do you have, and how are you determining
which records to put into your mailmerge?

 

[Sarah]

The two tables are donor info and donations. Some have just one date and
amount others have 2 or more. The idea is to be able to write one letter
that says "Thanks here are your donations 1/10/09, 1/20/09 for $50, $60. " So
if there was just one there would be no comma. Does that make since? Thanks
for your help.

 

[Sarah]

Just got it to work!!! Woohoo! Thanks for your help... Silly little error
that I made.