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

 

[herbivorous]

I think you'd want a VBA function that looped through the records, concatenating strings as you went along. For things like this, I often write function that take the user ID and return the string of information you want.

For you, something like this, which takes a record number:

Function EMAILFODDER(lngPrimary As Long) As String
'lngPrimary will pass the Record # along
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strEMAIL As String

Set db = CurrentDb
strSQL = ("SELECT [PEOPLEID], [AMOUNT], [Detail], [Date] FROM Table2 WHERE [RecordNo] =" & lngPrimary)

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

With rs
.MoveFirst

strEMAIL = Format([Date],"Short Date&quot&" "&FormatCurrency([Amount])&" "&[Detail]
.MoveNext

Do While Not rs.EOF 'goes until end of file is reached
rsDonations.MoveNext
strEMAIL = strEMAIL & "; " & Format([Date],"Short Date&quot&" "&FormatCurrency([Amount])&" "&[Detail]
Loop

EMAILFODDER = strEMAIL


End With
Set rs = Nothing
End Function

In Access 2000, with the DAO Reference checked under Tools References, this should return, for record 123:

5/5/2003 $150.00 Air; 5/6/2003 $25.00 Misc

Other summing and formatting and such can be done in similar fashion. If one record holds a lot of charges, you may have issues with string length, but I don't know how long is permitted. This may also have trouble if you send it to a non-existant record. You can put a safety in there, or just control via your query what records you're running this on.

I don't see any other reason why this wouldn't work, though I've certainly spent enough of my life trying to figure out why things that look like they should work don't.

 

[ImLost]

Sorry to be a pain, but....

I am a real novice in VBA. Where do I enter the above function and how do I call it? Am I correct in creating a module?

 

[herbivorous]

Yes, open a module and paste the above in and save it. Check to make sure that all the field names and table names are correct to what you have. Then you can enter it into your query grid as an expression (in place of a field along the top, if that makes sense.) Just type in EMAILFODDER([RecordNo]) -- it will look at the record number and put that string together for you for each record in the query.

Theoretically, anyway. I rarely manage to get a function working the first time through. I'm not much above novice level -- kind of a plugger, as opposed to an expert. This isn't so different from a function I've got already written so I think it won't be much trouble. I can try to help you troubleshoot it, as needed.