how to merge multiple number of record to the same page 2

[NEW2003]

what can i do to produce letter merge that look like this:

<000-00-0000>......<pathmark>, <shoprite>, <stop&shop> ....
<111-11-1111>......<a&p>, <wawa>....

from the data source look like this:

SS# store name
000-00-0000 pathmark
000-00-0000 shoprite
000-00-0000 stop&shop
111-11-1111 a&p
111-11-1111 wawa

 

[Fae]

You will have to be more specific, Are you working in Excel or word - Is your data and excel and you want to merge to word or is the data in word and you want to merge in word ???

Miracles we do immediately ...
imposibilities take a little longer.

 

[PeterMoran]

Hi NEW2003,

As Fae says its not easy, and I found that to be very much the case. If you are successful I think you can consider yourself a MailMerge expert.

Checkout this article:

WD97: How to Merge Conditional Number of Records to the Same Page (

http://support.microsoft.com/?kbid=105888)

- I did not find the article had sufficient explanation to be easy to follow, but eventually managed to understand enough by trying out all sorts of options based on the article, to achieve what I wanted.

and this may help also:

http://support.microsoft.com/support/word/usage/fields/default.asp

Also checkout this site for general mailmerge help:

http://mvps.org/word/FAQs/index6.html

Good Luck!

Peter Moran
Two heads are always better than one!

 

[NEW2003]

Fae, the data source is from ms access 2000 to word 2000 mail merge. I did check out the article that Peter Moran refer to. It helped alot but since my data from access look like this: name project
john 125x
john 254w
john 002a
leo 021w
leo 012w

I trouble trying to put an indicator in the table to mark the last record per name and punctuation. If you can help me out, the mail merge should look like this

......John's project 125x, 254w and 002a.......

 

[fneily]

I made a table with two fields - partno, desc. Desc can span more than one record, eg.
partno desc
abcd this is one desc
abcd this is two desc
xyz another desc
xyz more desc
etc. etc.

I want to concatenate the desc's into one field.

create a query that looks like this:
SELECT [partno], fConcatFld(&quot;parttable&quot;,&quot;partno&quot;,&quot;desc&quot;,&quot;string&quot;,[partno]) AS descs
FROM parttable
GROUP BY [partno];

Do ALT+F11, insert a new module and put the following code for the fConcatFld function:

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'Usage Examples:
' ?fConcatFld((&quot;Customers&quot;,&quot;ContactTitle&quot;,&quot;CustomerID&quot;, _
' &quot;string&quot;,&quot;Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb


loSQL = &quot;SELECT [&quot; & stFldToConcat & &quot;] FROM [&quot; & stTable & &quot;] WHERE [&quot; & stForFld & &quot;] = '&quot; & vForFldVal & &quot;' ;&quot; 'place this line on one line in the VBA window.

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.description
Resume Exit_fConcatFld
End Function

Run the query, which calls the function. Besure in your VBA window, click on Tools , the References and make sure the DAO library is referenced in the first three positions of the list.

You can then take the output of the query, do a make table and then run your mailmerge.

Neil

 

[NEW2003]

Neil, Thanks a lot for your time. I follow your instruction, but i got errors&quot;Wrong # of argument..&quot;. I kind of new to vba coding, so if you can point my mistake, i have the copy of the code below:
my MainTable contain [SS] and [PHARMACY]
the query:
SELECT MainTable.SS, MainTable.PHARMACY, fConcatFld(MainTable,[SS],[PHARMACY],&quot;string&quot;,[SS]) AS Expr1
FROM MainTable
GROUP BY MainTable.SS, MainTable.PHARMACY;

the function:
Function fConcatFld(stTable As String, stForFld As String, stForFldType As String, vForFldVal As Variant) As String
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
On Error GoTo err_fConcatFld
lovConcat = Null
Set lodb = CurrentDb
loSQL = &quot;Select [&quot; & stFldToConcat & &quot;] from[&quot; & stTable & &quot;] where [&quot; & stForFld & &quot;]=&quot; & vForFldVal & &quot;;&quot;
Set lors = lodb.openrecorset(loSQL, dbOPenSnapshot)
With lors
If .RecordCount <> 0 Then
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)
Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function
err_fConcatFld:
MsgBox &quot;Error#:&quot; & Err.Number & vbCtlf & Err.Description
Resume Exit_fConcatFld

End Function

Please,

Thanks

new2003

 

[fneily]

Your Select should look like:

Select [SS],fConcatFld(&quot;MainTable&quot;,&quot;SS&quot;,&quot;PHARMACY&quot;,&quot;string&quot;,[SS]) AS descs
From MainTable
Group By [SS];

The function doesn't change.

Neil

 

[NEW2003]

Neil, i fixed the query like below, and still get the same error &quot;Wrong # of argument used with function in query expression fConcatFld(&quot;MainTable&quot;,&quot;SS&quot;,&quot;PHARMACY&quot;,&quot;string&quot;,[SS])&quot;. What should i do?

Select [SS],fConcatFld(&quot;MainTable&quot;,&quot;SS&quot;,&quot;PHARMACY&quot;,&quot;string&quot;,[SS]) AS descs
From MainTable
Group By [SS];

Thanks again!
new user

 

[fneily]

I just ran this and it works, so lets' check some things.
Copy my function EXACTLY. The only thing you should make sure of is that the loSQL= statement is on the same line. Always copy code EXACTLY, even with the comments. Moving anyting can cause errors.
Make sure your fieldnames and tablename is correct.

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'Usage Examples:
' ?fConcatFld((&quot;Customers&quot;,&quot;ContactTitle&quot;,&quot;CustomerID&quot;, _
' &quot;string&quot;,&quot;Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb()

loSQL = &quot;SELECT [&quot; & stFldToConcat & &quot;] FROM [&quot; & stTable & &quot;] WHERE [&quot; & stForFld & &quot;] = '&quot; & vForFldVal & &quot;' ;&quot;

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.description
Resume Exit_fConcatFld
End Function

If it still won't work, email it to me at fneily@hotmail.com and I'll take alook at it. Because of Hotmail, make sure your database is 900K or less before shipping.