Concatenating two records into one

[ImLost]

Hello,

I have a fairly unique problem. I have two tables. First table contains the 'header' information (Unique detail per entry). Second table may contain several entries per 'header'. They are linked by an ID field. Data is entered using a Form (Table1) with a Continueous Subform (Table2). The findings recorded in the tables have to be mail merged and sent to addresses in the 'header' table.

The problem is that the data from the MakeTableQuery looks something like this:
ID RecordNo Detail Date Amount.....
1 123 Air 5/5/2003 150.00
1 123 Misc 5/6/2003 25.00
1 124 Park 5/7/2003 15.00
2 265 Rental 5/5/2003 200.00

Because of this structure the mail merge will send out 4 e-mails. The person associated with ID 1 will receive 2 e-mails regarding the same record no. What I would like to do is merge the Detail, Date, and Amount fields into the same record with RecordNo, Name, E-Mail....remaining constant. One RecordNo may have multiple Details associated with it.

Does this make sense? Is this even possible? Any alternatives?

Thank you for any suggestions.
Mike

 

[jrbarnett]

Mike

This situation is by no means unique, and your database is structured in a sensible way for storing the data; no changes are required for more efficient storage.

What are you using for the mailmerge? Are you using Word to a merge document, Access and the SendObject VBA code or something else? Your response to this will detail how I solve this problem.

John

 

[ImLost]

Thank you John,

Currently I'm using Word to merge. There is a long document where I have several merge fields.

Mike

 

[jrbarnett]

Mike,

I have had a long look at this and simulated your setup in a test database and must admit that this is a lot harder than I first thought, definitely something for M$ to consider for the next version of Word - to allow easy grouping on one or more fields and repeat values.

I have found a site detailing how to do this, but haven't yet got around to working out how it works and thus a solution that I would feel comfortable recommending to you, because I don't fully understand how and why it works myself, but if you want to have a go please visit

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#ComplexMerg

or

http://www.mvps.org/word/FAQs/MailMerge/

for more information on tweaking word mailmerges in general.

I have an alternative solution using Access, but it will probably be a lot of work on your part - it would make this grouping together part of the problem easier but cause problems elsewhere.
My idea is to create an access report with text on it, and put fields in the report, print it to a generic/text only printer driver on port file. The resulting file can then be copied and pasted into an email, but it won't be anywhere as automated as your system. Its practicality depends on the number of messages - with 3 it is probably usable, but much more it starts to become tedious.

John

 

[fneily]

Maybe this will give you help. Change the field names to yours.
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 record.

create a query that looks like this:
SELECT [partno], fConcatFld("parttable","partno","desc","string",[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(("Customers","ContactTitle","CustomerID", _
' "string","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 = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "] WHERE [" & stForFld & "] = '" & vForFldVal & "' ;" '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 near the top. Send your &quot;maketable&quot; through this.

Neil

 

[dpatrickcollins]

I have an alternate solution. Firstly, your &quot;business requirement&quot; seems to be that you need this one-record result in an access database table or query. This is because you are using mail-merge features. Secondly, no amount of querying is going to get you where you neeed to be. That being the case, create a procedure to take info from the two tables and populate a third. The third, essentially a temporary table, will be the source table for your mail merge query.

Sample code is provided below, but as with most code, execution is a little more wordy than purpose. So here's the synopsis of what the code does: it rolls through each row of the two-table query. For each set of rows with the same main table id (MainID), it populates a single row in the third table. That's it.

This assumes the third table would have multiple columns for each iterative value found in the second, or sub, table. For example, if your sub table's field was called &quot;OrderAmount&quot;, your third table would have &quot;OrderAmount1&quot;, &quot;OrderAmount2&quot;, etc. You would need as many iterative columns as the maximum number of repeating rows expected for any given main record. (By the way, if the maximum number cannot be determined, then your going to have problems with the mail merge end of things anyway).

Alas, the sample code:

Public Sub SampleProc()
Dim rstThirdTable As DAO.Recordset
Dim rstMultiRowQuery As DAO.Recordset
Dim intOldID As Integer

' Code for instantiating the recordsets go here
'let me know if you need help instantiating the
'recordsets
'*****************************************
'
'*****************************************

'Ensure query is sorted by &quot;main&quot; table ID for this
'procedure
With rstMultiRowQuery
Do Until .EOF
If !MainID = intOldID Then
'record belongs to previous main record so
'add to
'current row of third table
intFldNumber = intFldNumber + 1
rstThirdTable(&quot;Field&quot; & intFldNumber) = _
!SubFieldValue
Else
'record is a new main record so save current
'and add new record
If rstThirdTable.EditMode <> dbEditNone Then
rstThirdTable.Update
rstThirdTable.AddNew
Else
rstThirdTable.AddNew
End If

'reset field number
intFldNumber = 1
rstThirdTable(&quot;Field&quot; & intFldNumber) = _
!SubFieldValue

End If

intOldID = !MainID
.MoveNext
Loop
End With


End Sub

Hope this helps

 

[ImLost]

Thank you for all your replies!

Unfortunately I'm not familiar with VBA at all. It may as well be Greek. With that in mind I don't know what in the code to replace with my actual field names and what to leave as part of the code.

If anyone would be willing to provide me with an e-mail address I could send a small (250KB) sample database that shows exactly what I'm trying to do.

My e-mail address is ImLost3000@yahoo.com.

Thanks again.
Mike